MrExcel Publishing
Your One Stop for Excel Tips & Solutions

compare 2 columns


Posted by gary on October 19, 2001 8:46 AM

looking for a way to sort 2 columns with some common fields, some not. need to see the common entries on the same row.


Posted by Loren on October 19, 2001 9:39 AM

Re: compare 2 col: here are several ways

given 2 lists of words

1. Select column B (click the letter B)
2. Choose Format > Conditional formatting
" 3. In ""Condition 1"" choose ""Formula is"""
" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<>"")"
" 5. Click the ""Formats"" button and take your pick. OK.OK."

Now every name in B not in A will light up.

Another example
comparison of 2 columns
=IF(ISERROR(VLOOKUP(A1,$B$1:$B$2000,1,FALSE)),A1,"")
this function put in c1 and autofilled down will show the A1 value,
IF A1 does not exist anywhere in B1:B2000 -

3rd example...
Reply to: Compare two columns using wildcards
=ISNUMBER(MATCH(LEFT(A1,9)&"*",List2!A:A,0))+0

where A1 is a value from the 1st column and List2!A:A is
a reference to your 2nd column. This formula will return
0 or 1. 1 means that there's a match; 0 means there isn't.
You can apply a number format to change these values to any
flag you wish. For example, [=1][green]"Y";[red]"N" will
produce a green "Y" or a red "N".

------ Unique Rows Selection ----------

Has anyone done a macro that will retain the compare values in a column and will retain
the first of each unigue entry and delete the rows with repetitive data?

Answer 1
If you don't require a macro, you could highlight your social security row and goto data,
filter, advance filter. Check unique records only and okay. Note - dumps unique records,
not unique cells.
Highlight the collapsed list. Hit control G, special, visable cells only and control c
to copy. Then control V to paste your list on a new sheet with no duplicates.

If you tell the advanced filter to put the results in a different column (rather than
filtering the list in place) you can skip the copy paste as values steps.

. . . For a single column . . . . . . .
You can mark the duplicates, filter the list to display the marked entries, and delete
the visible rows.

1. Mark the duplicates. See Tagging Duplicate Entries at:
http://www.cpearson.com/excel/duplicat.htm
2. Apply a filter, using Filter>AutoFilter
3. Select the visible rows
4. Choose Edit>Delete