# Convert Vertical list to Horizontal

1. ## Convert Vertical list to Horizontal

I have check other methods for doing this but none of the solutions seem to match what I want.
My horizontal list is like this:
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
C 3
D 1
D 2
D 3

And I want it to look like this:

A B C D
1 1 3 1
2 2 2
3 3 3
4 4

If anyone can help with some formulas or vba code, I'd be grateful.

Thanx Paula.

2. ## Re: Convert Vertical list to Horizontal

Here's a formulas tinker which will deliver it ...

Assume your source data as posted is in A2:B13

The working area ...
Put in D1: =IF(COUNTIF(A\$2:A2,A2)>1,"",ROWS(\$1:1))
Copy down to D13
Put in E1:
=IF(COLUMNS(\$A:A)>COUNT(\$D\$2:\$D\$13),"",INDEX(\$A\$2:\$A\$13,SMALL(\$D\$2:\$D\$13,COLUMNS(\$A:A))))
Copy across to H1
Put in E2: =IF(\$A2=E\$1,ROWS(\$1:1),"")
Copy across to H2, fill down to H13

Now for the desired Results area ..
Put in J1: =E1, copy across to M1
Put in J2:
=IF(ROWS(\$1:1)>COUNT(E\$2:E\$13),"",INDEX(\$B\$2:\$B\$13,SMALL(E\$2:E\$13,ROWS(\$1:1))))
Copy across to M2, fill down to M13

3. ## Re: Convert Vertical list to Horizontal

Hi

One way could be this.

With your data starting in A2 & B2, in D2 put this Array Formula( Control+Shift+Enter) and copy down.

=IFERROR(INDEX(\$A\$2:\$A\$100;MATCH(0;COUNTIF(\$D\$1:D1;\$A\$2:\$A\$100);0));")

Highlight range F1:I1 and type this also Array formula.

=TRANSPOSE(D2:D5)

In F2 and copy down and across, put this also Array formula.

=IFERROR(INDEX(\$B\$2:\$B\$1000;SMALL(IF(\$A\$2:\$A\$1000=F\$1;ROW(\$B\$2:B\$1000)-1);ROW(B1)));"")

Change all the semi-colons in my formulas to comma.

4. ## Re: Convert Vertical list to Horizontal

Pl see attached file.

5. ## Re: Convert Vertical list to Horizontal

Thanks all for the help and quick responses. The INDEX formula worked exactly as I wanted. Paula.

6. ## Re: Convert Vertical list to Horizontal

New quick method:

Or you can use this way:
Go to the first post
Click edit
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1