Combine Item Usage Based on item, year and Month

[jsharpe]

HI All,
I have been trying to figure out a formula to combine column I for the same items in (C) that have the same month (f) and year (e). I need to pull this data into another spread sheet so it would have the item # (c), Use Month(F), Use Year (E), and Actual Usage (I) in separate columns, I attached the header for this sheet below. I just need to get this data usage combined properly then I can pull into the other workbook using xlookup. Thanks for the help.

other spread sheet data needs to be pulled into

 

[alansidman]

I cannot manipulate data in a picture. Please reload the same data to this site using XL2BB.

 

[jsharpe]

I installed XL2bb but the trust center is telling me its from an untrusted source and will not allow me to enable it.

 

[jsharpe]

original file

Item Usage by Warehouse.xlsx
	A	B	C	D	E	F	G	H	I
1	company	warehouse	item_num	usage_date	use_year	use_month	use_day	qual_usage	act_usage
2	01	01	03713981	2/8/2019	2019	2	8	2	2
3	01	01	MSF1436007	10/18/2018	2018	10	18	10	10
4	01	01	#1-25	5/18/2020	2020	5	18	3	3
5	01	01	#1-25	4/12/2021	2021	4	12	1	1
IC_USAGE_BY_WAREHOUSE

Below is the workbook I am trying to pull the data into

ItemHistory_MapFile-V.xlsx
	A	B	C	D	E	F	G	H
1	Required: I-A	I-B	I-C	I-D	I-E	I-F	I-G	I-H
2	Company ID	Location ID	Item ID	Year for Demand Period	Demand Period	Actual Usage	Number of Orders	Scheduled Usage
3	01	01	03713981	2019	2	#VALUE!
4	01	01	MSF1436007	2018	10
5	01	01	#1-25	2020	5
6	01	01	#1-25	2021	4
7	01	01	#25HOOKS2	2018	9
8	01	01	#25HOOKS2	2022	1
Item History

Cell Formulas
Range	Formula
C3:C102948	C3	='\\APPSVR1\DataConversionFiles\Export\Inventory\Item Usage by Warehouse.xlsx'!IC_USAGE_BY_WAREHOUSE[item_num]
D3:D102948	D3	='\\APPSVR1\DataConversionFiles\Export\Inventory\Item Usage by Warehouse.xlsx'!IC_USAGE_BY_WAREHOUSE[use_year]
F3	F3	=XLOOKUP(C3&D3&E3,'\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$C:$C&'\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$E:$E&'\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$F:$F,SUM('\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$I:$I))
E3:E8	E3	=XLOOKUP(C3&D3,'\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$C:$C&'\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$E:$E,TEXT('\\APPSVR1\DataConversionFiles\Export\Inventory\[Item Usage by Warehouse.xlsx]IC_USAGE_BY_WAREHOUSE'!$D:$D,"m"))
Dynamic array formulas.

 

[alansidman]

Here is a power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"usage_date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"item_num", "use_year", "use_month"}, {{"Total Usage", each List.Sum([act_usage]), type number}})
in
    #"Grouped Rows"

When you close and load from the PQ Editor, you select where you want the query to go.

 

[jsharpe]

Hi, thanks for the above solution it worked great. took a little to figure out since I had never used power query editor. Do you happen to know if their is a button in power query editor menu to use to limit the length of characters in a column? I currently use a TRIM formula to do this for each column.

 

[alansidman]

I think you have figured it out.

 

[jsharpe]

Not quite yet still trying to figure this out. below is an example of the workbooks im working on. you will see in row 4 that it has the max length of each column. is there a way to trim the length of each column easier than doing 1 column at a time?

orderquoteline-QUOTES.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	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Import Set Number	Line No	Item ID	Unit Quantity	Unit of Measure	Unit Price	Extended Description	Source Location ID	Ship Location ID	Product Group ID	Supplier ID	Supplier Name	Required Date	Expedite Date	Will Call	Tax Item	OK to Interchange	Pricing Unit	Commission Cost	Other Cost	PO Cost	Disposition	Scheduled	Manual Price Override	Commission Cost Edited	Other Cost Edited	Capture Usage	Tag and Hold Class ID	Contract Bin ID	Contract No.	Allocation Qty	Promise Date	Revision Level	Resolve Item Contract	Sample	Quote Line No.	Quote Complete	Item Description	Invoice No.	Line No
2	Alphanumeric	Numeric	Alphanumeric	Numeric	Alphanumeric	Numeric, Decimal	Alphanumeric	Numeric	Numeric	Alphanumeric	Numeric	Alphanumeric	Date Time	Date Time	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Numeric, Decimal	Numeric, Decimal	Numeric, Decimal	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Numeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Alphanumeric	Numeric
3	Required	Required	Required	Required	Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required	Not Required
4	8	9	40	10	8	19,4	255	9	9	8	9	50	8	8	1	1	1	8	19,4	19,4	19,4	1	1	1	1	1	1	8	8	8	14	8	20	1	1		1	40	8	9
5	1004	1	UB40 SEAL	30	CASE	15.046	UB40 Seals	100016	100016	METER	100411	Tight Seal	11/15/1999	11/13/1999	Y - Yes N - No	Y - Yes N - No	Y - Yes N - No	CASE	3.047	0.56	0.32	B - Backorder D - Direct ship S - Special H - Hold C - Cancel T - Transfer	Y - Yes N - No	Y - Yes N - No	Y - Yes N - No	Y - Yes N - No	Y - Yes N - No	Rough In	234	122345	24	12/12/2007	1A	Y - Yes N - No	Y - Yes N - No		Y - Yes N - No	Copper Tube – 12 Ft	127345	1
Sheet1

 

[alansidman]

Read this to understand column widths in Power Query. May not be the answer you are looking for but may help with understanding.

https://www.clearlyandsimply.com/clearly_and_simply/2020/05/adjust-power-query-table-style-and-properties.html