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.

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.

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.

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.