Home |
Search |
Today's Posts |
#1
|
|||
|
|||
compare two lists
I can export a client list from our 2003 and 2004 tax systems. They are in
xls format. Is there a way to run a compare on the name field in Excel and generate a report showing unique names in both lists. I don't care about matches. Or is there a way to get them into Access to do the same thing? Thanks. |
#2
|
|||
|
|||
Sure. Drop one list under the other and insert a column with a label for
each record of either 2003 and 2004 for each list respectively, eg:- Year Name 2003 a 2003 b 2003 c 2003 e 2003 g 2003 j 2003 l 2003 s 2003 t 2003 w 2003 y 2003 z 2004 b 2004 d 2004 e 2004 g 2004 h 2004 j 2004 l 2004 z 2004 w 2004 a 2004 q 2004 s 2004 p 2004 o 2004 i Put a couple of headers in, and now select the lot and do Data / Pivot table and Chart report, hit Next / Next / Finish. Drag Name inot the ROW fields and Year into the COLUMN fields. Now drag Name once again, but this time into the Data field. Now right click on the word name which will probably be in cell A4, choose Field Settings / Advanced / Click 'Descending' and choose 'using field - Count of name' and then hit OK. Now ignore all the totals where it says 2, and the list where all the totals are 1s below will be your list of unique names in each. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "rob p" wrote in message ... I can export a client list from our 2003 and 2004 tax systems. They are in xls format. Is there a way to run a compare on the name field in Excel and generate a report showing unique names in both lists. I don't care about matches. Or is there a way to get them into Access to do the same thing? Thanks. |
#3
|
|||
|
|||
Hi,
You may try this array formula (Ctrl+Shift+Enter): =if(or(exact(A1,$B$1:$B$50)),"",A1) and copy downwards A1 is from list 1. $B$1:$B$50 is list 2 Regards, Ashish Mathur "rob p" wrote: I can export a client list from our 2003 and 2004 tax systems. They are in xls format. Is there a way to run a compare on the name field in Excel and generate a report showing unique names in both lists. I don't care about matches. Or is there a way to get them into Access to do the same thing? Thanks. |
#4
|
|||
|
|||
I want to try this. First, is my first list A1 down? And then is my second
list B1 down? (and this means a total of 50 records?) ( I actually have about 1,200 of them.) Do I load one list in column A1 and then copy and paste in the other list to the second column? I'm not quite sure of what I need to do. Thanks. "Ashish Mathur" wrote in message ... Hi, You may try this array formula (Ctrl+Shift+Enter): =if(or(exact(A1,$B$1:$B$50)),"",A1) and copy downwards A1 is from list 1. $B$1:$B$50 is list 2 Regards, Ashish Mathur "rob p" wrote: I can export a client list from our 2003 and 2004 tax systems. They are in xls format. Is there a way to run a compare on the name field in Excel and generate a report showing unique names in both lists. I don't care about matches. Or is there a way to get them into Access to do the same thing? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare 2 columns, and create a list of items that are in both lists | Excel Worksheet Functions | |||
Compare two different size lists | Excel Worksheet Functions | |||
How do I compare lists and eliminate duplictates | Excel Discussion (Misc queries) | |||
Can I compare 2 lists to combine duplicate entries in new list? | Excel Worksheet Functions | |||
How do I compare two lists of names in excel? | Excel Discussion (Misc queries) |