Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Transpose Five Column Array To A Single Column

1. I agreed to these rules
Join Date
12th October 2006
Posts
9

## Transpose Five Column Array To A Single Column

A B C D E
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

You have a data array of numbers appearing in columns A - E. The numbers appear in the order indicated above. The data array can vary from 1 to 7000 numbers depending on data downloaded to Excel. Therefore, the data will appear from Row 2 to possibly Row 1400. The goal is to place the numbers in order of appearance down a single column.
For instance if the results were to appear in column G the would appear in the following cell reference order:
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2
A3
B3
C3
D3
E3
.
.
.

Excel Video Tutorials / Excel Dashboards Reports

2. Established Member
Join Date
5th August 2003
Posts
531

## Re: Transpose Five Column Array To A Single Column

I think I have a solution, but it assumes the following:

1) No data gaps:
All cells in an upper row or further left column will be filled before any in a lower row or further right column (that is, if there's a value in C3, then there must have been values in A1:E1, B2:E2, and A3:B3)

2) there will only be 5 columns and up to 1400 rows (this is easier to modify -but the provided formula conforms to these rules).

Try the following formula in G1:

VB:
```=If(CELL("row", F1)<=COUNT(\$A\$1:\$E\$1400), INDEX(\$A\$1:\$E\$1400,INT((CELL("row", F1)-1)/5)+1,MOD(CELL("row", F1)-1, 5)+1), "")

```
Copy it down to G7000.

If assumption 1 isn't true, maybe a macro approach is the way to go. Post back if that's the case.

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Transpose Five Column Array To A Single Column

Hi,

In G1 and copied down,

=IF(ROWS(\$A\$1:\$A1)<=COUNT(\$A\$1:\$E\$3),INDEX(\$A\$1:\$E\$3,INT((ROWS(\$A\$1:\$A1)-1)/5)+1,MOD(ROWS(\$A\$1:\$A1)-1,5)+1),"")

HTH

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno