Results 1 to 5 of 5
Thread: Comparing Cells (Excel2000)

20011107, 23:14 #1coachgolemGuest
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???

20011108, 15:22 #2
 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>

20011108, 17:52 #3
 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

20011109, 16:06 #4coachgolemGuest
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

20011109, 17:29 #5
 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