Combine 2+ list to compare data within each

DaveAtlanta

Board Regular
Joined
Nov 9, 2005
Messages
100
Good Afternoon

I have multiple list of data with many of the items duplicated within each list, but the data relating differ. What I'm trying to do is combine the list so I can print different datacolumns from each for easy comparison. This situation reminds me of a matching test with items in both columns that need to be matched together.

How can I align the items into one sheet, but maintaining the integrity of data within each list?

I appreciate any suggestions and help
Dave
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Are your two lists of the same length? And are there any items missing on either list from the other?
 
Upvote 0
Are your two lists of the same length? And are there any items missing on either list from the other? If yes to both then just do data, sort and sort both lists the same way by primary key first and everything should match.
Otherwise you might consider vlookup. How many records do you have?
 
Upvote 0
I think this will work okay, but after removing duplicate codes I have just over a 1000 items to compare within 4 different list.

Is VLookUp still the better solution?

Also, I need help hiding all the '#N/A' that appear for unmatched items. Normally I can use an IF() to have it be "" (blank) instead of '#N/A' but I am having trouble incorporating that with this function.

Any ideas?

Again thank you for you help
Dave
 
Upvote 0
Hiding the #na's can be accomplished by nesting your vlookup in an IF using ISERROR:

Code:
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:C,1,0)),"",VLOOKUP(A2,Sheet2!A:C,1,0))


To explain a little, this says if your vlookup generates an error show nothing (or "") otherwise show the vlookup.

Trivia:
You can use 0 instead of false for the final term of the vlookup.
 
Upvote 0

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top