# Transpose does not work



## pto160 (Jan 5, 2023)

I am on the latest version of Excel 365. 
I am trying to convert a horizontal range to a vertical range. The first row would be one column and next row would be in another column. Transpose does not seem to work.

Book1ABCD1NameIDInvoice NumberAmount2John Smith100A10050034Result I want56NameJohn Smith7ID1008Invoice NumberA1009Amount500Sheet3


----------



## Fluff (Jan 5, 2023)

How about
Fluff.xlsmABCD1NameIDInvoice NumberAmount2John Smith100A10050034Result I want56NameJohn Smith7ID1008Invoice NumberA1009Amount500ListsCell FormulasRangeFormulaA6:B9A6=WRAPCOLS(TOROW(A1:D2),4)Dynamic array formulas.


----------



## johnnyL (Jan 5, 2023)

Or for a macro approach how about:


```
Sub TransposeToVertical()
'
    Dim TransposeArray  As Variant
'
    TransposeArray = Range("A1:D2")
'
    Range("A6").Resize(UBound(TransposeArray, 2), UBound(TransposeArray, 1)) = Application.Transpose(TransposeArray)
End Sub
```


----------



## Flaiban (Jan 5, 2023)

Or...

```
Sub TransposetoVertical_v2()
  Dim r As Range
  Dim i As Long
  
  Set r = Range("A1", Range("D" & Rows.Count).End(xlUp))
  For i = 2 To r.Rows.Count
    Union(r.Rows(1), r.Rows(i)).Copy
    Range("A" & Rows.Count).End(xlUp).Offset(2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
  Next i
  Application.CutCopyMode = False
End Sub
```


----------



## pto160 (Jan 5, 2023)

Thanks so much everyone. There could also be a possibility that rows I want are not contiguous.
Is the possible to still use the wrap columns and to rows?

Book1ABCD1NameIDInvoice NumberAmount2Joe Doe200A2002003Peter Smith300A506004John Smith100A10050056Result I want7NameJohn Smith8ID1009Invoice NumberA10010Amount500Sheet3


----------



## Fluff (Jan 5, 2023)

How about
Fluff.xlsmABCD1NameIDInvoice NumberAmount2Joe Doe200A2002003Peter Smith300A506004John Smith100A10050056Result I want7NameJohn Smith8ID1009Invoice NumberA10010Amount500ListsCell FormulasRangeFormulaA7:A10A7=TOCOL(A1:D1)B7:B10B7=TOCOL(A4:D4)Dynamic array formulas.


----------



## pto160 (Jan 5, 2023)

Thanks so much. This really helps.


----------



## Peter_SSs (Jan 5, 2023)

pto160 said:


> Transpose does not seem to work.


I'm wondering what the issue with Transpose is/was? Can you elaborate?

For me, with your original data

23 01 06.xlsmABCD1NameIDInvoice NumberAmount2John Smith100A1005003456NameJohn Smith7ID1008Invoice NumberA1009Amount500TransposeCell FormulasRangeFormulaA6:B9A6=TRANSPOSE(A1:D2)Dynamic array formulas.


Or for the non-contiguous data

23 01 06.xlsmABCD1NameIDInvoice NumberAmount2Joe Doe200A2002003Peter Smith300A506004John Smith100A10050056NameJohn Smith7ID1008Invoice NumberA1009Amount500Transpose (2)Cell FormulasRangeFormulaA6:B9A6=TRANSPOSE(CHOOSEROWS(A1:D4,1,4))Dynamic array formulas.


----------



## alansidman (Jan 5, 2023)

Here is an alternative solution with Power query


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


----------



## pto160 (Monday at 10:32 PM)

I am just wondering if it is possible to use this with a Lamda?
So for example, the Lamda would be* Lamda(A,B)*

A would represent range A, which in the example above would be A1:D1
B would represent range B, which in the example above would be A4:D4


----------



## pto160 (Jan 5, 2023)

I am on the latest version of Excel 365. 
I am trying to convert a horizontal range to a vertical range. The first row would be one column and next row would be in another column. Transpose does not seem to work.

Book1ABCD1NameIDInvoice NumberAmount2John Smith100A10050034Result I want56NameJohn Smith7ID1008Invoice NumberA1009Amount500Sheet3


----------



## pto160 (Monday at 10:37 PM)

Peter_SSs, I did not see your reply as I did not refresh the page when I replied about the lamda. That is an excellent solution.  I must have pressed the wrong button for the Transpose function. Thanks so much.
Thanks alansidman for the PowerQuery solution as well.


----------



## Peter_SSs (Monday at 11:04 PM)

You're welcome. Thanks for the follow-up.


----------



## pto160 (Yesterday at 5:43 PM)

```
=TRANSPOSE(CHOOSEROWS(A1:D4,1,4))
```

Peter_SSs, I am just wondering if this can be combined with CHOOSECOLS. Say you want to only select column A, Column C and Column D.
Is this possible?


----------



## Peter_SSs (Yesterday at 8:28 PM)

pto160 said:


> I am just wondering if this can be combined with CHOOSECOLS. Say you want to only select column A, Column C and Column D.


Sure, you can use CHOOSEROWS again or CHOOSECOLS

23 01 13.xlsmABCD1NameIDInvoice NumberAmount2Joe Doe200A2002003Peter Smith300A506004John Smith100A10050056NameJohn Smith7Invoice NumberA1008Amount500910NameJohn Smith11Invoice NumberA10012Amount500TransposeCell FormulasRangeFormulaA6:B8A6=CHOOSEROWS(TRANSPOSE(CHOOSEROWS(A1:D4,1,4)),1,3,4)A10:B12A10=TRANSPOSE(CHOOSEROWS(CHOOSECOLS(A1:D4,1,3,4),1,4))Dynamic array formulas.


----------



## pto160 (Yesterday at 9:14 PM)

Absolutely fantastic. Thanks so much. This will save me so much time working with data.


----------



## Peter_SSs (Yesterday at 9:21 PM)

Cheers.


----------

