Spreadsheet data comparison

[SCO7TY]

Hi guys, brand new to this forum and pretty much an amateur on Excel so I'll try and explain what I need help with as best as possible.

I upload what is called cap data (information on every make and model of cars) to my website via Excel spreadsheets. Now i have 3 funders that send me there pricing every month and i upload this data to my website.

My problem is, I receive 4 different variants of pricing on 4 different spreadsheets e.g. 2/3 year pricing, maintained and non maintained per funder.

One spreadsheet can have 50,000 entries, another might have 40,000, 45,000 and 55,000. None of them match.

What I'm hoping i can do is some how compare all 4 spreadsheets or the data in each of them on one spreadsheet and delete the data that doesn't appear on all 4 spreadsheets.

Really difficult to explain but hopefully someone can understand what I'm after?

Thanks

[wenqq3]

Hi SCO7TY,
Welcome to the forum. Please attached a sample worksheet (4 diff worksheet that you mention),and expected result.
Remove those sensitive data. Thanks

Post a WORKBOOK
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[SCO7TY]

Thanks for the reply, i'll attach two spreadsheets. There are just two columns in each spreadsheet - price and code. Basically I need to compare both cap code columns in each spreadsheet and then delete the code along with the price next to it if it doesn't appear in both spreadsheets. I have deleted some of the data out of the spreadsheets as they were too big to upload.

3YR2.xlsx4YR2.xls

[wenqq3]

Okie.
Apply this formula in 1 worksheet (i.e 4YR2).
C3 =

Please Login or Register  to view this content.

Drag down.

Those reply #N/A mean not found at 3YR2.

[wenqq3]

Other idea:
since you will combine those 4 different workbooks into 1 worksheet.
You can copy those code into 1 new sheet (same column). Then advanced filter (choose Unique Records Only).
Then you may apply
B1 =vlookup(A1,4YR2's range<$B3:$B50000>, 2, FALSE)
C1 =vlookup(A1,3YR2's range<$B3:$B50000>, 2, FALSE)
D1 =vlookup(A1,5YR2's range<$B3:$B50000>, 2, FALSE)
E1 =vlookup(A1,6YR2's range<$B3:$B50000>, 2, FALSE)

Note: i not using Index and Match, so please make your code in Col A and FINANCE_RENTAL at Col B
Then select ALL, ctrl + G.
Special > Formala > tick ERRORS only.
Right click 1 of the selected cell, choose Delete, Entire Row.
Then other appear is all appear in 4 diff sheets.
Hope it help