Tuesday, November 25, 2008

Excel Ninjutsu: Highlighting similar items in 2 columns

I was investigating a case that required me to compare and analyze multiple hosts' requests to similar external sites. In this case, I already have the list of IP addresses. I just needed a way to compare the data. Here's a quick way to do this in Excel.

1. Copy the data to be compared in 2 columns
2. Click Format > Conditional Formatting
3. Choose "Formula is" in the drop down menu
4. Use the countif() function to search the items. $A:$B are the columns being compared. Change this, based on your need.

=COUNTIF($A:$B,B1)>1

5. Choose the format color > Click Ok

Similar items are now highlighted.