If qty in each group have duplicate value, the formula for customer order no will be changed to INDEX+AGGREGATE.Bosco_yip Sir,
I have observed if the same qty is continue in any column (BUS, TRUCK, JEEP & MOTORCYCLE), then the incorrect customer order no is reflecting.
Should the "Material qty" have duplicate value, "Customer order" and "Prod Code" formula will be changed to >>Bosco_Yip Sir,
Please find attached the Example File (BY)-1 for your reference.
This is rather easy to do with Power Query. But that depends on your Office version and OS. Office 2010 or higher on Windows is required.
Depending on your version you can download the add-on or it's already on your ribbon under Get & Transform. The steps are the same.
Load data from Table/Range. I named the range tData.
Select while holding control key the first 2 columns.
Then right mouse click and from the context menu select unpivot other columns
Arrange the columns in the order of your liking.
Sort and filter, the actions are much alike as in ordinary excel.
Save and load to Excel as Table.
Should the "Material qty" have duplicate value, "Customer order" and "Prod Code" formula will be changed to >>
In "Output sheet" F6, copied right to G6 and all copied down :
=INDEX('Data Sheet'!E:E,AGGREGATE(15,6,ROW('Data Sheet'!E$6:E$24)/(INDEX('Data Sheet'!$G$6:$J$24,0,MATCH($E6,'Data Sheet'!$G$5:$J$5,0))=$H6),COUNTIFS($E$6:$E6,$E6,$H$6:$H6,$H6)))
View attachment 68118