VBA to bring columns in rows (except first column)

[Dampa88]

Dear all,

I'm struggling with a code to move columns values into rows, except the first column.
Of course, it will be needed to add rows and populate the blanks in the first column.

Probably, an example will make my exercise easier to understand:

An important point is that the number of columns may change.

I have been able to add a certain number of rows after the first one and transpose the values.
Then I'm blocked when I need to move to the next value of the first columns.

I know that there is a easier code, I hope you can give me some inputs.

Thanks,
D

 

[alansidman]

This is a simple unpivot using Power Query.

Here is the Mcode for that procedure. Look at the links in my signature for more information on this Excel function.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique", type text}, {"InfoA", type text}, {"InfoB", type text}, {"InfoC", type text}, {"InfoD", type text}, {"InfoE", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Unique"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

2​

[/td][td]

A​

[/td][td]

InfoA​

[/td][td]

Home​

[/td][/tr]


[tr][td]

3​

[/td][td]

A​

[/td][td]

InfoB​

[/td][td]

North​

[/td][/tr]


[tr][td]

4​

[/td][td]

A​

[/td][td]

InfoC​

[/td][td]

West​

[/td][/tr]


[tr][td]

5​

[/td][td]

A​

[/td][td]

InfoD​

[/td][td]

Sunny​

[/td][/tr]


[tr][td]

6​

[/td][td]

A​

[/td][td]

InfoE​

[/td][td]

Hot​

[/td][/tr]


[tr][td]

7​

[/td][td]

B​

[/td][td]

InfoA​

[/td][td]

Office​

[/td][/tr]


[tr][td]

8​

[/td][td]

B​

[/td][td]

InfoB​

[/td][td]

Sud​

[/td][/tr]


[tr][td]

9​

[/td][td]

B​

[/td][td]

InfoC​

[/td][td]

East​

[/td][/tr]


[tr][td]

10​

[/td][td]

B​

[/td][td]

InfoD​

[/td][td]

Cloudy​

[/td][/tr]


[tr][td]

11​

[/td][td]

B​

[/td][td]

InfoE​

[/td][td]

Cold​

[/td][/tr]
[/table]

 

[Dampa88]

Thanks for the very fast reply and for the hint!
I'd never think about unpivot.

KR,
D