Excel: Conditional Format Rows in One List that are in Another List by David Hager

 

Here is the scenario. You have a long list with multiple columns and a second list containing records to look up in the first list. All of the records in each row of the first list must be compared with all of the records in the second list.

Here is the conditional formatting formula that will highlight the desired rows. Although it appears surprisingly simple, it took me quite a while to come up with this.

=FIND(CONCAT($A2:$E2),CONCAT($I$2:$M$4))

This formula looks for the concatenated row string in the long string from list 2 and if the FIND function finds the string, a value corresponding to the position of the string is returned. Any number of 1 and above is interpreted in CF as TRUE. Otherwise, the formula produces an error, which is interpeted as FALSE.

This CF formula was applied to list 1 starting at A2.

As you can see from the following figure, the expected rows in list 1 are highlighted.

 TableCompareCF1

Now, if your data happened to be all numbers in each column, a row could be hightlighted by coincidence due to a match in the large concat string. So, don’t use this technique with lists of that characteristic.

I am sure that you will find this technique useful.

You can download the example file here.

TableCompareCF

Advertisements

5 thoughts on “Excel: Conditional Format Rows in One List that are in Another List by David Hager

  1. Pingback: Excel: Conditional Format Rows in List 1 that are Not in List 2 by David Hager | Excel For You

  2. Pingback: #Excel Super Links #81 – shared by David Hager | Excel For You

  3. Peter Bartholomew

    David
    Have you any idea why conditional formatting should not accept a Boolean array? One is reduced to ‘tacky’ relative referencing using the conditional formatting calculation engine that will not even evaluate range intersections!
    The formula I would wish to use is
    =COUNTIFS(category1, category2, type1, type2)
    but, instead, I have to mess around with
    =COUNTIFS(category1, category2.item, type1, type2.item)
    where ‘category2.item’ refers to the intersection
    = category2 row
    and ‘row’ refers to “=7:7” or “=8:8” depending on where the active cell is (or “=R” using a more rational notation).

    Reply
  4. Pingback: #Excel Super Links #143 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s