Summarise SKU arrivals in table-consolidate multiple lines into one and group by week

[imdumb]

Hi everyone,

I'm hoping someone can help.

I have the attached a doc which has two tabs, one with the data that i get out of our ERP [Data] and the second being how i need to summarise the data [Desired Table Format].

The Data tab provides the SKU information along with delivery data and the quantity that will arrive on the date, problem being i have multiple entries for a SKU which means i won't always get a concise report.

I've searched the web on how to present this data horizontally, and the formula below (when you rename the ranges) is good:

=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),"")

BUT filling it across 1400 rows and upto 20 (have only provided sample data) columns takes nigh on forever to provide the data, in addition i can't get it to divide the quantities in the week brackets as they are presented in [Desired Table Format].

I hope this all makes sense and someone can help? I'm not sure if there is a way i can utilise power query and / or pivot tables to get the data presented in the 2nd tab or not?

I'd really appreciate all your help! Thanking you in advance!

[alansidman]

create a pivot table as shown in the attached.

[imdumb]

ofcourse! thank yoU!