Forum Discussion
Need help with sumifs, vlookup, etc type question
I am on Excel 2010.
I have two sets of data from two different systems and I'm trying to audit the data output. Each has a date, a part number, and a quantity. They are on different sheets of the same workbook (data dump from respective ERP systems).
I want to bring quantities from Sheet II over to Sheet I, as long as they meet the same date and part number as Sheet I. The final would look like this:
Date / Part# / Quantity / Sheet II quantity
There are thousands of part numbers, with rev changes and new part numbers all the time, so setting them as a, "product" to reference doesn't make sense as that would have to be maintained.
How would I go about doing this?
In D2 on Sheet1:
=IFERROR(INDEX('Sheet II'!$C$2:$C$1000, MATCH(1, ('Sheet II'$A$2:$A$1000=A2)*('Sheet II'!$B$2:$B$1000=B2), 0)), "")
Change Sheet II to the actual name of the second sheet, and adjust the ranges if your data extend below row 1000.
Confirm the formula by pressing Ctrl+Shift+Enter, then fill down.
- This doesn't seem to be working for me - and I think it's because there is no 1:1 quantity.
The data from one source many have multiple entries (no idea why) and looks like this:
Sheet I date, part #, quantity = 10
Sheet II same date, same part #, quantity = 5
same date, same part #, quantity = 2
same date, same part #, quantity = 3
Which sounds like a sumif(s) type function would be needed...?=SUMPRODUCT('Sheet II'!$C$2:$C$1000, ('Sheet II'$A$2:$A$1000=A2)*('Sheet II'!$B$2:$B$1000=B2))
