• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Arrangement of Data

sambit

Member
Dear Sir,
I have two sheets i.e, Data Sheet and Output Sheet.
The data transfer from the datasheet to the output sheet in a prescribed manner.

please help to resolve the issue.

I have attached an example file for your kind information.
 

Attachments

GraH - Guido

Well-Known Member
Hell

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.
 

Attachments

Marc L

Excel Ninja
Hi sambit,​
which issue ? Error message, what have you done ?​
As here it's the Excel forum did you try a pivot ?​
Which kind of solution do you expect for ?​
 

bosco_yip

Excel Ninja
Formula solution

1] "Output sheet" E6, copied down :

=LOOKUP(ROW(A1),COUNTIF(OFFSET('Data Sheet'!F$6:F$24,,,,COLUMN('Data Sheet'!A$1:E$1)),">0")-(ROW('Data Sheet'!F$24)-ROW('Data Sheet'!F$6)),'Data Sheet'!G$5:J$5)

2] "Output sheet" F6, copied right to G6 and all copied down :

=INDEX('Data Sheet'!E$6:E$24,MATCH($H6,INDEX('Data Sheet'!$G$6:$J$24,0,MATCH($E6,'Data Sheet'!$G$5:$J$5,0)),0))

3] "Output sheet" H6, copied down :

=INDEX('Data Sheet'!G:J,AGGREGATE(15,6,ROW('Data Sheet'!G$6:J$24)/(INDEX('Data Sheet'!G$6:J$24,0,MATCH(E6,'Data Sheet'!G$5:J$5,0))>0),COUNTIF(E$6:E6,E6)),MATCH(E6,'Data Sheet'!G$5:J$5,0))

68108
 

Attachments

Last edited:

sambit

Member
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.
 

bosco_yip

Excel Ninja
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.
If qty in each group have duplicate value, the formula for customer order no will be changed to INDEX+AGGREGATE.

Further details and information are required

Regards
 
Last edited:

bosco_yip

Excel Ninja
Bosco_Yip Sir,
Please find attached the Example File (BY)-1 for your reference.
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)))

68118
 

Attachments

Last edited:

sambit

Member
Hell

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.
GraH-Guido Sir,
The above solution is very useful for getting the desired output.

A lot of thanks for the tips.
 

sambit

Member
Bosco_Yip Sir,
It's working fine.

I really appreciate it.




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
 
Top