How to add up quantities that are associated ONLY with an "Identifier word" like a SKU or word across 2 or 3 sheets in the same workbook?

[jbillh]

Hi Folks,

So, we're trying to figure out how to add up quantities in multiple cells that contain specific "Identifier words" ONLY in the same row as that specific "identifier word" across 2 or 3 sheets in the same workbook? The identifiers words like "SKU" or "butter" in the example, are not always in the same rows since we download data that changes row positions frequently. The quantities to add up will ALWAYS be in the same row as the identifier words but may not always be in the same column. That said, the column "name" will always be "units-1" or "units-2"

I hope the attached helps make more sense of this!

Thanks Kindly,

Bill

 

[jbillh]

You bet! We used actual data in our process and had to work through some issues but here 'tis:

These three formulas are found on the results/ "Combined Inventory" sheet but refer to the two data sheets.

=UNIQUE(VSTACK(FILTER(VSTACK(OFFSET('FBA-Inventory'!D:D,1,0,COUNTA('FBA-Inventory'!D:D)-1)),VSTACK(OFFSET('FBA-Inventory'!D:D,1,0,COUNTA('FBA-Inventory'!D:D)-1))<>0),VSTACK(FILTER(VSTACK(OFFSET('AWD-Inventory'!C:C,1,0,COUNTA('AWD-Inventory'!C:C)-1)),VSTACK(OFFSET('AWD-Inventory'!C:C,1,0,COUNTA('AWD-Inventory'!C:C)-1))<>0))))


=IF(A2<>"",SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!E:E)+SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!G:G)+SUMIF('FBA-Inventory'!D:D,$A2,'FBA-Inventory'!M:M),"")


=IF(A2<>"",IFNA(IF(MATCH(A2,'Combined inventory'!A:A,)>0,"YES",""),"NO"),"")


These are the only formulas I can find. The third one is "dynamic" to sorta run a verification on the second one given there are some variations in the SKU's.

Is there something else you'd like to see?

Thanks for your help and I hope this is of use to someone! Bill

 

[Peter_SSs]

Is there something else you'd like to see?

Yes, since the formulas are not entirely clear to me.

With XL2BB, and any sensitive data disguised, could you post a smallish section of

• 'AWD-Inventory' that shows say 10 rows and columns B:G
• 'FBA-Inventory' that shows say 10 rows and columns D and M (the columns between can be hidden before creating the XL2BB Mini Sheet to keep it small)
• 'Combined Inventory' that shows a few rows with your above formulas in place. I am assuming your first formula above is in column A, but if not please include column A as well.

Just a thought in the interim though, does this formula produce the same results as the long first one above? I have assumed that the headings in D1 of FBA and C1 of AWD are the same. If they are different then change the very last 1 in my formula below to 2

=DROP(UNIQUE(TOCOL(HSTACK('FBA-Inventory'!D:D,'AWD-Inventory'!C:C),1)),1)

 

[jbillh]

Ok, folks... here you go. This is the answer an Excel Pro came up with for us. I hope this is useful somehow.

"FBA Inventory"

MR-EXCEL-inventory-report-dynamic-formula-Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Country	Product Name	FNSKU	Merchant SKU	ASIN	Condition	Supplier	Supplier part no.	Currency code	Price	Sales last 30 days	Units Sold Last 30 Days	Total Units	Inbound	Available	FC transfer	FC Processing	Customer Order	Unfulfillable	Working	Shipped	Receiving	Fulfilled by	Total Days of Supply (including units from open shipments)	Days of Supply at Amazon Fulfillment Network	Alert	Recommended replenishment qty	Recommended ship date	Recommended action	Unit storage size
2	US	Widget A	4	ABC-350	B02496	New	Supplier 1		USD	1	99	0	25	0	0	0	0	0	0	0	0	0	Amazon			out_of_stock	0	none	No action required
3	US	Widget B	2	CBA-150	B03248	New	unassigned		USD	3	87	0	12	36	0	0	0	0	0	0	0	0	Amazon			out_of_stock	0	none	No action required	0.0555 ft3
4	US	Widget C	3	XYZ-100	B08792	New	unassigned		USD	21.8	24	0	64	0	0	0	0	0	0	0	0	0	Amazon			out_of_stock	0	none	No action required	0.567 ft3
5	US	Widget D	1	JKL-250	B03321	New	Supplier 1		USD	104.5	19	0	3	0	0	0	0	0	0	0	0	0	Amazon	0	0	out_of_stock	0	none	No action required	0.0559 ft3
FBA-Inventory

"AWD Inventory"

MR-EXCEL-inventory-report-dynamic-formula-Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Product Name	SKU	FNSKU	ASIN	Available in AWD (units)	Available in AWD (cases)	Inbound to AWD (units)	Inbound to AWD (cases)	Outbound to FBA (units)	Available in FBA (units)	Available in FBA (days)
2	Widget D	ABC-250	1	B03321	0	0	0	0	300	0	22
3	Widget B	CBA-150	2	B03248	100	2	46	0	50	52	23
4	Widget C	XYZ-100	3	B08792	400	4	0	0	0	262	60
5	Widget A	JKL-350	4	B02496	100	2	24	0	50	46	52
AWD-Inventory

"Combined Inventory"

MR-EXCEL-inventory-report-dynamic-formula-Sample.xlsx
	A	B
1	SKU	Combined Quantity
2	ABC-350	25
3	CBA-150	158
4	XYZ-100	464
5	JKL-250	3
Combined inventory

Cell Formulas
Range	Formula
B2:B5	B2	=SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!E:E)+SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!G:G)+SUMIF('FBA-Inventory'!D:D,$A2,'FBA-Inventory'!M:M)

"Combined Dynamic"

MR-EXCEL-inventory-report-dynamic-formula-Sample.xlsx
	A	B	C	D	E
1	SKU	Combined Quantity			Does it Exists on Combined
2	ABC-350	25			YES
3	CBA-150	158			YES
4	XYZ-100	464			YES
5	JKL-250	3			YES
6	1
7	2
8	3
9	4
Combined Dynamic

Cell Formulas
Range	Formula
A2:A9	A2	=UNIQUE(VSTACK(FILTER(VSTACK(OFFSET('FBA-Inventory'!D:D,1,0,COUNTA('FBA-Inventory'!D:D)-1)),VSTACK(OFFSET('FBA-Inventory'!D:D,1,0,COUNTA('FBA-Inventory'!D:D)-1))<>0),VSTACK(FILTER(VSTACK(OFFSET('AWD-Inventory'!C:C,1,0,COUNTA('AWD-Inventory'!C:C)-1)),VSTACK(OFFSET('AWD-Inventory'!C:C,1,0,COUNTA('AWD-Inventory'!C:C)-1))<>0))))
B2:B5	B2	=IF(A2<>"",SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!E:E)+SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!G:G)+SUMIF('FBA-Inventory'!D:D,$A2,'FBA-Inventory'!M:M),"")
E2:E5	E2	=IF(A2<>"",IFNA(IF(MATCH(A2,'Combined inventory'!A:A,)>0,"YES",""),"NO"),"")
Dynamic array formulas.

 

[Peter_SSs]

Thanks for the samples. First some comments.

• I would generally try to avoid whole column References in formulas. In some cases that can make formulas very slow to calculate, having to deal with over a million rows. In my suggestions below I have used down to row 1000 but if you need bigger just change that number. Even if you had to make it 100,000 that is still less than 1/10 the number of cells in a whole column.
  
  
• I would also try to avoid volatile functions like OFFSET as that can also badly affect the performance of your worksheet.
  
  
• With your Excel version it is often possible to avoid copying formulas down the column like you have in column B of 'Combined Dynamic'. (I'm not sure why those formulas stopped at row 5 either but perhaps that was just an oversight.

Anyway, if what you have shown in 'Combined Dynamic' is what you want then certainly the column A values can be obtained with a much sorter, more efficient and non-volatile formula as shown in G2.
My formula in H2 is a bit longer than the one you have in col B, but it does not need to be copied down.

.. and if you did want all those results with a single formula (ie both columns at once) I have provided an option in cell J2

jbillh.xlsm
	A	B	C	D		F	G	H	I	J	K
1	SKU	Combined Quantity					SKU	Combined Quantity		SKU	Combined Quantity
2	ABC-350	25					ABC-350	25		ABC-350	25
3	CBA-150	158					CBA-150	158		CBA-150	158
4	XYZ-100	464					XYZ-100	464		XYZ-100	464
5	JKL-250	3					JKL-250	3		JKL-250	3
6	1						1	0		1	0
7	2						2	0		2	0
8	3						3	0		3	0
9	4						4	0		4	0
10
Combined Dynamic

Cell Formulas
Range	Formula
A2:A9	A2	=UNIQUE(VSTACK(FILTER(VSTACK(OFFSET('FBA-Inventory'!D:D,1,0,COUNTA('FBA-Inventory'!D:D)-1)),VSTACK(OFFSET('FBA-Inventory'!D:D,1,0,COUNTA('FBA-Inventory'!D:D)-1))<>0),VSTACK(FILTER(VSTACK(OFFSET('AWD-Inventory'!C:C,1,0,COUNTA('AWD-Inventory'!C:C)-1)),VSTACK(OFFSET('AWD-Inventory'!C:C,1,0,COUNTA('AWD-Inventory'!C:C)-1))<>0))))
G2:G9	G2	=LET(sku,UNIQUE(TOCOL(HSTACK('FBA-Inventory'!D2:D1000,'AWD-Inventory'!C2:C1000),1,1)),sku)
H2:H9	H2	=LET(awdb,'AWD-Inventory'!B2:B1000,BYROW(G2#,LAMBDA(r,SUMIF(awdb,r,'AWD-Inventory'!E2:E1000)+SUMIF(awdb,r,'AWD-Inventory'!G2:G1000)+SUMIF('FBA-Inventory'!D2:D1000,r,'FBA-Inventory'!M2:M1000))))
J2:K9	J2	=LET(bc,'AWD-Inventory'!B2:C1000,b,TAKE(bc,,1),sku,UNIQUE(TOCOL(HSTACK('FBA-Inventory'!D2:D1000,TAKE(bc,,-1)),1,1)),HSTACK(sku,BYROW(sku,LAMBDA(r,SUMIF(b,r,'AWD-Inventory'!E2:E1000)+SUMIF(b,r,'AWD-Inventory'!G2:G1000)+SUMIF('FBA-Inventory'!D2:D1000,r,'FBA-Inventory'!M2:M1000)))))
B2:B5	B2	=IF(A2<>"",SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!E:E)+SUMIF('AWD-Inventory'!B:B,$A2,'AWD-Inventory'!G:G)+SUMIF('FBA-Inventory'!D:D,$A2,'FBA-Inventory'!M:M),"")
Dynamic array formulas.

 

[jbillh]

Thanks Peter! What you say makes good sense to me... with my limited understanding of formulas and functions. I'll make sure my right-hand man sees this and has a chance to evaluate if we should make some mods accordingly. While we haven't used it enough yet to see it perform slowly or such, we don't expect our downloads to exceed 500 lines of data, at the most, ever. We should probably dial-in those parameters. On the Dynamic sheet... I included the 1, 2, 3, 4 but that's only because I could change them from their original values but couldn't delete them without changing the code beyond my understanding. So far... we are thrilled with the way this works as it takes what is often a 2 - 3 hour manual process and allows us to figure things out in under a few minutes, from download to final pasting into another sheet for "mostly accurate and mostly current" inventory numbers. We are feeling very grateful for Excel and folks that know how to run it way better than us! Thanks Kindly and Take Good Care!

 

[Peter_SSs]

You're welcome. Hope it all works out for you. Thanks for the follow-up.