MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorting...Sort of....


Posted by Robert Mathews on August 16, 2001 5:41 AM

All,

I have a excel workbook with 3 worksheets. Each sheet has 2 column's of text data. What I need help with is, I want to compare the first 2 worksheets to the third worksheet and delete all duplicates from the third worksheet, leaving only a list of differences.

Help!!!!!!!

Robert Mathews
rpmathe@sandia.gov


Posted by Barrie Davidson on August 16, 2001 8:04 AM

Robert, are you comparing both columns of data or just one of the two?

Barrie

Posted by Robert Mathews on August 16, 2001 9:55 AM


Comparing just one of the two, The first column has the list name, the second column has the list type. There are only 3 different types of lists. The first two list are being compared to the third (the master) and I want a final list that shows what names are missing from the first 2 on the third. Clear as mud....


Posted by Barrie Davidson on August 16, 2001 11:13 AM


Okay Robert, here is my understanding of your problem. You have a list of data (in Sheet3) that you want to determine those data points that have no corresponding match in either Sheet2 or Sheet1. The match is based upon the "list type" which is in column B on all three sheets. Now assuming your data in Sheet3 is in columns A and B, try putting this formula in C1 and copying down (you'll have to change the lookup range from $B$1:$B$23 to whatever the proper range is):

=IF(OR(ISNA(VLOOKUP(B3,Sheet1!$B$1:$B$23,2,0))=TRUE,ISNA(VLOOKUP(B3,Sheet2!$B$1:$B$23,2,0))=TRUE),"Match","No Match")
Hope this helps out. If not, let me know (on this board) and I'll try to help you out again.

Regards,
Barrie

Posted by Robert M on August 16, 2001 1:00 PM

Barry,


I think we're close;

worksheet 1 column A List name
worksheet 1 column B Location This is same for entire list
worksheet 2 column A List name
worksheet 2 column B Location This is same for entire list
worksheet 3 column A List name
worksheet 3 column B Location This is same for entire list

In every worksheet column A contains the list name unique for that list but could match the master list. Column B contains the list location ie. like ServerA or listfolder. This name is constant for that entire list. I want to match worksheet A to Worksheet C, and Worksheet B to Worksheet C, C is the master, If the entry matches, its there already, if not I need to know about it and make the change.

Thanks Much.....

Rob

Posted by Barrie Davidson on August 16, 2001 1:30 PM

Now we're getting somewhere!! Now my understanding is that we are checking column A in Sheet1 to find any records not matched in Sheet3. We are doing the same thing in Sheet2. If this is correct, input this formula in C1 in Sheet1 and Sheet2 and copy down (and change the range "$A$1:$A$23" to the correct range).

=IF(ISNA(VLOOKUP(A1,Sheet3!$A$1:$A$23,2,0)),"No Match","Match")

Is my understanding correct?

Barrie

Posted by Robert Mathews on August 16, 2001 2:03 PM

Barrie,

This works great THANKS!!!!!!!


Robert