+ Reply to Thread
Results 1 to 4 of 4

compare two lists

  1. #1
    rob p
    Guest

    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. #2
    Ken Wright
    Guest

    Re: compare two lists

    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" <nospam*wlb@stans.net> wrote in message
    news:e%23XrqLDzFHA.1168@TK2MSFTNGP15.phx.gbl...
    >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. #3
    Ashish Mathur
    Guest

    RE: compare two lists

    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. #4
    rob p
    Guest

    Re: compare two lists

    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" <mathurashish@hotmail.com> wrote in message
    news:8D05C2F0-D68E-4493-9D2B-8B7D07039D36@microsoft.com...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1