Transpose multiple horizontal rows to a singel vertical column

pete1229 · Aug 18, 2022

Is there a simple way (equation or VBA) to transpose hundreds of horizontal lines to a single vertical line?

The data covers 48 columns from A to AV and I would like column AX to contain the transposed data. I did a quick example using only the first row transposed to AX. Data from the next row would continue down the AX column until there are no more rows.

test.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

1

2

1.9

2

1.9

2

1.9

2.1

2.6

2

1.9

2.5

2

2.1

3.3

4.2

4.4

4.5

5.1

7.2

5.5

5.1

5.5

6.3

5.3

4.5

4.8

5.9

5.1

4

4.1

3.7

4

3.5

2.9

2.7

1.8

1.9

1.8

1.9

2

1.9

1.8

2

2.8

2

1.8

2

2.6

1.9

1.8

2.4

2.6

2.4

2.5

3.5

4.2

4.4

5

4.9

4.7

4.5

3.7

2.5

2.1

2

1.8

2

1.8

1.9

2

1.8

1.9

3

1.9

1.8

1.9

1.8

2

2.4

1.9

1.8

2

1.8

1.9

2

1.9

1.8

1.9

1.8

1.9

1.8

1.9

1.8

1.9

2

4

1.9

2

2.5

1.9

2.5

2.3

3

3.4

3.7

5.1

4.4

4.3

4.4

4.1

4.6

6

5.9

6.3

4.8

3.7

4.2

4.6

4.4

4

3.5

3.3

2.4

1.9

1.8

2.2

1.8

1.9

5

2

6

1.9

7

2.1

8

2.6

9

2

10

1.9

11

2.5

12

2

13

2.1

14

2.1

15

3.3

16

4.2

17

4.4

18

4.5

19

5.1

20

7.2

21

5.5

22

5.1

23

5.5

24

5.5

25

6.3

26

5.3

27

4.5

28

4.8

29

5.9

30

5.1

31

4

32

4.1

33

3.7

34

4

35

3.5

36

2.9

37

2.7

38

1.8

39

1.9

40

1.8

41

1.8

42

1.8

43

1.8

44

1.8

45

1.8

46

1.8

47

1.8

48

1.9

Sheet3

Fluff · Aug 18, 2022

How about

Excel Formula:

=LET(rng,A1:AV4,c,COLUMNS(rng),s,SEQUENCE(c*ROWS(rng),,0),INDEX(rng,INT(s/c)+1,MOD(s,c)+1))

pete1229 · Aug 18, 2022

Fluff said:
How about

Excel Formula:

=LET(rng,A1:AV4,c,COLUMNS(rng),s,SEQUENCE(c*ROWS(rng),,0),INDEX(rng,INT(s/c)+1,MOD(s,c)+1))

Well that was lightning fast

Thank you, works perfectly!

Fluff · Aug 18, 2022

Glad to help & thanks for the feedback.

Transpose multiple horizontal rows to a singel vertical column

pete1229

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

pete1229

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Transpose multiple horizontal rows to a singel vertical column

pete1229

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

pete1229

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock