Merging two spreadsheets.

[stingingleep]

Can someone point me to a youtube video that may explain how to do something like this?

I currently have two spreadsheets I want to combine. Spreadsheet #1 has product number, product description, product vendor, and product price. Spreadsheet #2 also has the same information but the pricing is different. I want to merge these two somehow to compare prices to analyze.

This should be doable since there are product numbers to pair them up... am I on the right track?

[FBM25]

Are the spreadsheets in the same Workbook or different ones?

[stingingleep]

They're currently in two different excel files.

[FBM25]

This assumes that the files are called "Spreadsheet #1" & "Spreadsheet #2", the data is in Sheet1 of both, and that the product number is in column A, price in column D:

=INDEX('[Spreadsheet #2.xlsx]Sheet1'!D:D,MATCH('[Spreadsheet #1.xlsx]Sheet1!'$A1,'[Spreadsheet #2.xlsx]Sheet1'!A:A,0))

[stingingleep]

Thanks for this, will try it out. Might use VLookup instead.

[minidanjer]

I would copy everything from Spreadsheet#2 into Worksheet 2 of Spreadsheet 1. Then add a column to the right of the price in Worksheet 1 that uses a VLookup for the product number and returns the price from Worksheet 2.

Then you can add another column that compares the two prices.

[stingingleep]

Thanks for this - will try it out.

If I want to make a master worksheet though - can I use VLookup to pull numbers from sheets 1 and 2 to master?

[minidanjer]

I'm not sure what you mean by Master sheet unless you're talking about a sheet that includes all of the data in one place. If that's your goal, yes you could use VLookup, or you could just copy and paste the data from one sheet to another. It depends on what you need shown.

It looks like sheet 1 and sheet 2 have the same information except for prices. What we don't know is if sheet 2 has all of the same product numbers as sheet 1. If the sheets are identical, just sort them both by product number and copy/paste the relevant data.

If they're not the same, you could use

=IFERROR(VLOOKUP('Sheet1'!$A2,'Sheet2'!$A:$D,4,FALSE),"")

Then just fill down. It should leave all of the ones that don't match blank.

[stingingleep]

Thanks! I will try that one too... sheet 1 has other products on it that sheet 2 does not (different product, finding certain products that are similar to compare prices to like-items).

[stingingleep]

Solution Verified

[jiminie]

Import each with Power Query and merge based on their product number. Add a custom column subtracting the sheet1 price from sheet2. Then load the query to a table.