Help me find (and change color of) similar data in Excel!

October 10, 2011 11:08 AM

Excel 2007 issue: I'm trying to have Excel mark matching data in two columns with some kind of color or formatting.

I have two columns of data relating to codes in an AS400 database.

Basically, I need to see if any logins from a set of usernames shows up on another, much larger list of usernames so I can make further changes to them.

I have all the data in Excel, but I don't know what v-lookup formula I should use.

Ideally, I'd like to make matching usernames turn a color (say orange) so I can easily spot them and do the work I need to do.

I apologize if I'm not being crystal clear here, please feel free to ask for clarification.

posted by lattiboy to Computers & Internet (3 answers total)

Best answer: Assume you have a username list in column D, and you want to match these usernames to a longer list in column A (rows 1-20).

You want conditional formatting on D1 that says: =NOT(ISNA(VLOOKUP(D1;$A$1:$A$20;1;FALSE))) -- this will find those items that ARE in A also. Just choose the formatting you like for this rule. You are using a formula to determine which cells to edit.

Change D1 to the location of the specific cell you are creating the conditional formatting on, change $a$1:$a$20 to the longer list -- be sure to use the $s. Then copy formatting (I think itès called format painter) onto the rest of the list.
posted by jeather at 11:28 AM on October 10, 2011 [1 favorite]

Those should be commas, not semi-colons in jeather's formula, i.e.:

=NOT(ISNA(VLOOKUP(D1,$A$1:$A$20,1,FALSE)))
posted by desjardins at 11:54 AM on October 10, 2011 [1 favorite]

I was copy/pasting from a French version, sorry.
posted by jeather at 2:00 PM on October 10, 2011