Results 1 to 5 of 5
  1. #1
    coachgolem
    Guest

    Comparing Cells (Excel2000)

    I have two worksheets in my file that I want to compare data from the same two columns in each, to see if there are any duplicates. Each sheet has 19 columns, but the two I want to compare are labeled " PN " and " Acct# ". I want to compare the new sheet with the old sheet, to know what's in the new sheet that is not in the old. I can do this with a VLOOKUP if only comparing one column in each sheet. Now that I have a combination of two columns that I must compare in each sheet, I am lost and frustrated. Any Ideas How???

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Comparing Cells (Excel2000)

    coachgolem

    What you can do is insert a new column and Concatenate the two PN and Acct# columns say with a dash. Now this will represent the same data in both columns. Now use this new column for your VLookup. Do the same on the other sheet and compare the two new combined columns.

    Extra Tip: if you want to do something special with the duplicates you could use an If Statement with your VLookup something like:

    =IF(ISERROR(VLOOKUP("PN_Column","Combined_Column", 1,FALSE)),1/0,VLOOKUP("PN_Column","Combined_Column",1,FALSE))

    Now you need to use the proper ranges and not the names I used, if the VLookup does not find it it puts a 1/0 which would become an error, then you can say Goto Special Formula Errors and Excel will highlight all these cells identifying the cells you want to work with.

    Sine this is Thursday where I am, this tip comes free of charge... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing Cells (Excel2000)

    If the original PN column is A1:A6, the original Acct# column is B1:B6, the new PN Column is D, and the new Acct# column is E, the the following formula ARRAY ENTERED (hold down Shift+Ctrl when pressing Enter) into F1 (or any other cell) will count the number of times that the first PN and Acct# are duplicated in A1:A6 and B1:B6. You can then copy this formula down next to the new column to get a count of each combination of new PN anf Acct#. Any count that comes up zero is not duplicated in the old columns.

    <pre>=SUM(($A$1:$A$6=D1)*($B$1:$B$6=E1))
    </pre>

    Legare Coleman

  4. #4
    coachgolem
    Guest

    Re: Comparing Cells (Excel2000)

    Thanks to you and Wassim for the great answers. Both will work.
    I will add that it can also be done with the data in two different sheets...using Legare's Array:

    =SUM((Old!$A$1:$A$6=D1)*(Old!$B$1:$B$6=E1))

    I don't understand the use of the * in this formula and how it makes it work? Silly question, I know, but can you explain? I fully understand all the other info and it's functionality. I just gots to know!

    Mickey

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing Cells (Excel2000)

    In Excel, False is represented by zero, and True is represented by 1. Therefore, by multiplying the result of the two comparisons together, you get zero if either or both is false and 1 if both are true. The Sum then just gets a count where both are true.
    Legare Coleman

Posting Permissions

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