Help with pulling from a report

[shiz11713]

I'm using software that exports reports to excel that look like this:

I need to pull Units Sold to the appropriate cell in another sheet:

The best I can figure is a two-condition xlookup, but I have no idea how to do that while combining sales from the same month.

 

[awoohaw]

Welcome to the forum. This can be done in Power Query reasonably fast. How familiar are you with it?

Take a look at this video:

 

[Alex Blakenburg]

Your initial layout is actually pretty clean.
You can either do a manual fill down of the "Product or Service" or use a formula helper column(s).
In the past I used helper columns to the right so that you could use it as a template and just drop in the new data each time.

Power Query can also do this for you and do some of the other summarisation as well as @awoohaw suggested. I think he has chosen the wrong video though (its 20 min long) since your data is pretty close to the ideal format and already in an unpivoted format. All you need is a Fill Down in the first column and then Filter out the unwanted rows. You can then either get PQ to add the month information and/or summarisation or you can do the rest in Excel.

Since you have a list of Product Names prepopulated that you want to use, see if the below gives you any ideas.
You don't need to Line Type column but it can be helpful for applying filtering or using it a filter for a pivot table that you overlay on to that data.

20240525 Clean up and Pivot shiz11713.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Product or Service	Job#	Date	Qty	Prod	Line Type		Output --->	Product Name	January 24	February 24	March 24	April 24	May 24
2									18/4 SHLD, Belden	182	0	220	178	0
3	18/4 SHLD, Belden				18/4 SHLD, Belden				3/4" NM Flex Connector	0	10	0	2	0
4		1007	25/01/2024	182	18/4 SHLD, Belden	Detail
5		1067	13/03/2024	220	18/4 SHLD, Belden	Detail
6		1109	5/04/2024	178	18/4 SHLD, Belden	Detail
7					18/4 SHLD, Belden
8		Jobs:		Units Sold:	18/4 SHLD, Belden	Detail
9		3		580	18/4 SHLD, Belden	Total
10					18/4 SHLD, Belden
11	3/4" NM Flex Connector				3/4" NM Flex Connector
12		1034	26/02/2024	10	3/4" NM Flex Connector	Detail
13		1129	18/04/2024	2	3/4" NM Flex Connector	Detail
14					3/4" NM Flex Connector
15		Jobs:		Units Sold:	3/4" NM Flex Connector	Detail
16		2		12	3/4" NM Flex Connector	Total
Raw Data

Cell Formulas
Range	Formula
J2:N3	J2	=SUMIFS($D:$D,$E:$E,$I2,$C:$C,">="&J$1,$C:$C,"<="&EDATE(J$1,1)-1)
E3:E16	E3	=IF(A3<>"",A3,E2)
F3:F16	F3	=IF($B3="","",IF($B2="Jobs:","Total","Detail"))

 

[shiz11713]

Thanks so much!

 

[Alex Blakenburg]

You're welcome. Glad we could help.