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

Need help converting data from column to row subhead

kalaate

New Member
Hello friends,
I had searched a lot, maybe the terms i used aren't right, i am unable to find any such similar problems and its solution

I have a table of data as given below
RAW DATA
Customer010B50010B52MNP012NGL011MGL012
3GEN POLY
10​
20​
10​
BDP POLY
5​
5​
10​
15​
ADDY MOULD
10​
MN PLASTIQUE
10​
20​

I want to convert this to
DESIRED DATA
CustomerProductQty
3GEN POLY010B50
10​
MNP012
20​
MGL012
10​
BDP POLY010B50
5​
010B52
5​
MNP012
10​
MGL012
15​
ADDY MOULDNGL011
10​
MN PLASTIQUE010B50
10​
010B52
20​



Requesting seniors and experts to help how to do it in excel maybe through functions or macro or whichever.
My raw data is huge, and i need to find a time saving way to do it.

Regards

kalaate
 

vletm

Excel Ninja
kalaate
Have You search it with Forum Rules writings?
You should reread it.
especially How to get the Best Results at Chandoo.org
After You've read - You'll know what to do next.
 

kalaate

New Member
kalaate
Have You search it with Forum Rules writings?
You should reread it.
especially How to get the Best Results at Chandoo.org
After You've read - You'll know what to do next.
I did search the forum as well as on google using the same search terms " converting column to row sub head"
unable to find my desired solution.
Like I said, May be my search term are not accurate, I am not sure which is to be the search term for this. AndI have been doing manually, but that is taking up a lot of time.

Thus i finally posted my problem here,

I am looking at solution that is maybe function based, or vba/macro based that will make it easier.
 

AlanSidman

Well-Known Member
Since you are using 2016, Power Query is an option for you to unpivot the data. Here is the Mcode to unpivot the data

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
Data Range
A
B
C
D
E
F
2
Customer​
010B50​
010B52​
MNP012​
NGL011​
MGL012​
3
3GEN POLY​
10​
20​
10​
4
BDP POLY​
5​
5​
10​
15​
5
ADDY MOULD​
10​
6
MN PLASTIQUE​
10​
20​
7
8
Customer​
Attribute​
Value​
9
3GEN POLY​
010B50​
10​
10
3GEN POLY​
MNP012​
20​
11
3GEN POLY​
MGL012​
10​
12
BDP POLY​
010B50​
5​
13
BDP POLY​
010B52​
5​
14
BDP POLY​
MNP012​
10​
15
BDP POLY​
MGL012​
15​
16
ADDY MOULD​
NGL011​
10​
17
MN PLASTIQUE​
010B50​
10​
18
MN PLASTIQUE​
010B52​
20​
 

Attachments

Last edited:

vletm

Excel Ninja
kalaate
... Have You search it with Forum Rules writings?
Seems You skipped this basic sentence
>> For the best/fastest results, Upload a Sample File... especially an Excel-file
without above, others could only guess eg about Your used layout.
 

kalaate

New Member
kalaate
... Have You search it with Forum Rules writings?
Seems You skipped this basic sentence
>> For the best/fastest results, Upload a Sample File... especially an Excel-file
without above, others could only guess eg about Your used layout.
Oh.. right.. sorry about that, I pasted in the post.. Will remember..
thankyou..
Since you are using 2016, Power Query is an option for you to unpivot the data. Here is the Mcode to unpivot the data

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
Data Range
A
B
C
D
E
F
2
Customer​
010B50​
010B52​
MNP012​
NGL011​
MGL012​
3
3GEN POLY​
10​
20​
10​
4
BDP POLY​
5​
5​
10​
15​
5
ADDY MOULD​
10​
6
MN PLASTIQUE​
10​
20​
7
8
Customer​
Attribute​
Value​
9
3GEN POLY​
010B50​
10​
10
3GEN POLY​
MNP012​
20​
11
3GEN POLY​
MGL012​
10​
12
BDP POLY​
010B50​
5​
13
BDP POLY​
010B52​
5​
14
BDP POLY​
MNP012​
10​
15
BDP POLY​
MGL012​
15​
16
ADDY MOULD​
NGL011​
10​
17
MN PLASTIQUE​
010B50​
10​
18
MN PLASTIQUE​
010B52​
20​
thankyou sir for the reply... let me check this..
 

kalaate

New Member
kalaate
... Have You search it with Forum Rules writings?
Seems You skipped this basic sentence
>> For the best/fastest results, Upload a Sample File... especially an Excel-file
without above, others could only guess eg about Your used layout.
Oh Im sorry, i should have posted the excel file, had pasted in the post itself.
WIll remember..
thankyou
 

vletm

Excel Ninja
kalaate
... why an Excel -file ? ...
Should I guess that Your 'data' would start from cell H11?
Is it really table or do it look like a table?
Is Your expected layout as Your given visual image?
 

kalaate

New Member
kalaate
... why an Excel -file ? ...
Should I guess that Your 'data' would start from cell H11?
Is it really table or do it look like a table?
Is Your expected layout as Your given visual image?
Wow..
Well I wouldn't expect you to guess. I agree My bad that i didnt share an excel file to make the problem more specific and clear. I sincerely apologised for it.

And yes, the response I got has resolved my issue and I am thankful to the forum for helping out.
 

kalaate

New Member
Since you are using 2016, Power Query is an option for you to unpivot the data. Here is the Mcode to unpivot the data

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
Data Range
A
B
C
D
E
F
2
Customer​
010B50​
010B52​
MNP012​
NGL011​
MGL012​
3
3GEN POLY​
10​
20​
10​
4
BDP POLY​
5​
5​
10​
15​
5
ADDY MOULD​
10​
6
MN PLASTIQUE​
10​
20​
7
8
Customer​
Attribute​
Value​
9
3GEN POLY​
010B50​
10​
10
3GEN POLY​
MNP012​
20​
11
3GEN POLY​
MGL012​
10​
12
BDP POLY​
010B50​
5​
13
BDP POLY​
010B52​
5​
14
BDP POLY​
MNP012​
10​
15
BDP POLY​
MGL012​
15​
16
ADDY MOULD​
NGL011​
10​
17
MN PLASTIQUE​
010B50​
10​
18
MN PLASTIQUE​
010B52​
20​
This has helped resolved my problem, thankyou so much.

Regards
 
Top