Pivot Table Data

Question

Hi,

I receive orders from a customer in the highlighted green section attached. In order to import the data into our Order System - we need to convert the data into the format in orange - columns J-M

How can this be done faster please as opposed to manually? It needs to incorporate their Customer PO (column H) too - as product codes appear more than once in the file.

Answer 1

This one should work. I missed “*5” in third formula.

=HSTACK(INDEX(H2:H38,ROUNDUP(SEQUENCE(COUNTA(H2:H38)*5)/5,0)),INDEX(A2:A38,ROUNDUP(SEQUENCE(COUNTA(A2:A38)*5)/5,0)),INDEX(C1:G1,MOD(SEQUENCE(COUNTA(A2:A38)*5)-1,5)+1),TOCOL(C2:G38))

Answer 2

Or you can use formula to convert it.

=HSTACK(INDEX(H2:H38,ROUNDUP(SEQUENCE(COUNTA(H2:H38)*5)/5,0)),INDEX(A2:A38,ROUNDUP(SEQUENCE(COUNTA(A2:A38)*5)/5,0)),INDEX(C1:G1,MOD(SEQUENCE(COUNTA(A2:A38))-1,5)+1),TOCOL(C2:G38))

Answer 3

Is it possible to share a test file? Then I can make changes on it and send it back to you?

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.  *Please make sure you have removed any sensitive or private information in the sample file before uploading.

Answer 4

Hmm I am doing this on a Mac - I cannot see these options.

Answer 5

Try Power query unpivot function.

1.Select your data range, Data>from table/Range.

2. Remove unit column, Press Ctrl to select Vendor Style column and PO column

Go to Tranfrom Tab>Unpivot other columns

3. Adjust the column location renname the header and Load result to Excel.