# Transpose multiple rows and columns into one column with ignoring blanks



## Cafofo (Dec 12, 2022)

Following from this thread: Transpose multiple rows and columns into one column with ignoring blanks


DanteAmor said:


> It is undoubtedly a memory problem for your machine and your excel. As I told you I have no problems with 100,000 lines and columns up to DZ.
> You will have to perform executions, exit excel, to clean the memory of your machine and return to executions.
> 
> Try this
> ...


Hey @DanteAmor, I was looking for something similar and find your solution. Thank you for that.

But I have only one adicional need , when transposing the whole table I need to copy the first Row of Each colum to the the side column "B" of the "Sheet2" as well.
My table has many numbers(dates) and all first rows are months. So I have to copy the Month as well( something like this )

Any idea? I tried to use hLookup but it doest not help that much


----------



## Cafofo (Dec 14, 2022)

Just to be sure that It can be undestood, the code provided by the coleage @DanteAmor in another topic provided a solution to transpose the first table(left) to another sheet but only the entire table. I have to do something similar but I have to copy the first row for each column as well for all coresponding lines as possible to check into the image bellow.


----------



## Cafofo (Dec 18, 2022)

Does anyone can help me with that?


----------



## alansidman (Dec 19, 2022)

Power Query Solution


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"
```


----------

