Help with pulling from a report

shiz11713

New Member
Joined
Apr 24, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I'm using software that exports reports to excel that look like this:

Sheet 1.png



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

Sheet 2.png

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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:
 
Upvote 0
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
ABCDEFGHIJKLMN
1Product or ServiceJob#DateQtyProdLine TypeOutput ---> Product NameJanuary 24February 24March 24April 24May 24
218/4 SHLD, Belden18202201780
318/4 SHLD, Belden18/4 SHLD, Belden 3/4" NM Flex Connector010020
4100725/01/202418218/4 SHLD, BeldenDetail
5106713/03/202422018/4 SHLD, BeldenDetail
611095/04/202417818/4 SHLD, BeldenDetail
718/4 SHLD, Belden 
8Jobs:Units Sold:18/4 SHLD, BeldenDetail
9358018/4 SHLD, BeldenTotal
1018/4 SHLD, Belden 
113/4" NM Flex Connector3/4" NM Flex Connector 
12103426/02/2024103/4" NM Flex ConnectorDetail
13112918/04/202423/4" NM Flex ConnectorDetail
143/4" NM Flex Connector 
15Jobs:Units Sold:3/4" NM Flex ConnectorDetail
162123/4" NM Flex ConnectorTotal
Raw Data
Cell Formulas
RangeFormula
J2:N3J2=SUMIFS($D:$D,$E:$E,$I2,$C:$C,">="&J$1,$C:$C,"<="&EDATE(J$1,1)-1)
E3:E16E3=IF(A3<>"",A3,E2)
F3:F16F3=IF($B3="","",IF($B2="Jobs:","Total","Detail"))
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top