Comparing Lists of Numbers in Excel 2003

[JenDen]

I have 2 looong lists of hundreds of account ID numbers. I need to compare the lists and find which, if any, of the same ID numbers appear on both lists. These are on 2 different spreadsheets, and I must compare to identify which accounts appear on both spreadsheets. I am using Excel 2003.

Somewhat new to this world... Any help would be appreciated.

 

[NARAYANK991]

Hi ,


The following link explains this.


http://support.microsoft.com/kb/213367


Let us assume one of your workbooks is Book1 , and the other is Temp.


Let us assume the list of account IDs is from A7:A1274 in Book1 ; and that the list in the other workbook is H15:H1300 in Temp.


Type in ( or copy + paste ) the following formula in any one cell away from your list of numbers , say B7 , in any one of the workbooks , say Book1 :


=IF(ISERROR(MATCH(A7,[Temp.xlsm]Sheet1!$H$15:$H$1300,0)),"",A7)


Copy this formula to the remaining cells ( B8 through B1292 ).


The list in column B will show blanks where the IDs are different ; wherever the IDs appear in both the lists , the ID will be displayed.


Narayan

 

[Jack]

Hi Jenden


Hi NARAYANK991 you beat me to it - I had already written this so thought may as well still send as another slightly different approach although much the same concept:


Copy this in say WorkbookB C2 (assuming your list in WorkbookA is a1:A1000


=IF(COUNTIF([BookA]Sheet1!$A$1:$A$1000,A1)>1,"Duplicate!","<")


and copy this down to the end of your list in WorkbookB Col A. You can easuly adjust the dimensions. If the woorhsheet tab is not Sheet1 then obviously change this as well. The "<" is just a non distracting symbol to show unique values but you can do as N has done and make it "" .


You can also add conditional formatting to highlight the Duplicate! flags if you want say red cells with white bold font. Maybe you know how to do this?


Note I have assumed there aren't duplicates in the same list in the same workbook. If there are and you need to identify these let me know.


cheers

John

 

[NARAYANK991]

Hi John ,


Sorry ! There's always room for a different and better opinion.


Narayan

 

[kmcarl]

I need to do something very similar to this - however, I need the list of unique values to be compact - they can't span the whole spreadsheet. And I still need them to dynamically update as items are added to the lists.


For example - in your example above, it would produce the values in the column labeled Unique1. I need the values in the column labeled Unique2. BTW: In my list, if it helps, no item will ever be in Column B if it is not already in Column A.


Animals1 Animals2 Unique1 Unique2

Cow Horse Cows Cow

Pig Chicken Horse

Horse Cow Horse Sheep

Chicken

Sheep Sheep


Thanks for any help you can give me!

 

[NARAYANK991]

Hi Carl ,


In your example , why does "Chicken" not appear in either Unique1 or Unique2 ?


Can you clearly spell out the criteria for an entry to appear in Unique1 column ? Similarly for Unique2 ?


Narayan