Create Multiple Columns from Two Column Excel

Question

Here is what I have:

column a      column b
32            apple
32            pear
32            banana
33            soap
33            tea

Here is what I would like:

32      apple      pear      banada
33      soap       tea

I don't want to use VBA or anything complex, just a simple formula please!

Generally speaking, you should leave your data as is. It is already Excel-friendly and will work well with formulas like COUNTIF/S, SUMIF/S, SUMPRODUCT, etc. It is also in a desirable format for using a pivot table if you'd prefer. — tigeravatar
– tigeravatar, Commented Nov 2, 2013 at 1:25
Hello. This does not work with a pivot table as there are close to 3000 rows and when a pivot table is used there is many gaps in the resulting table. I need to compile a CSV file from the two columns and as such, this is why I need it setup this way. Many thanks! — ecomqueen
– ecomqueen, Commented Nov 4, 2013 at 4:18

rwking · Accepted Answer · 2013-11-01 22:21:40Z

1

You can select the vertical range in Column B that you wish to be separated

right click and "Copy"
select the place you wish to paste it horizontally
right click and choose "Paste Special"
in the dialogue box that appears, select "Transpose" near the bottom, and press OK.

It should paste your selection into consecutive horizontal cells. Then you can go back and delete any extra rows.

Hope this helps!

edited Nov 1, 2013 at 22:21

answered Nov 1, 2013 at 22:13

rwking

1,0326 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ecomqueen Over a year ago

Hello. I know this, however, this does not help me. If you look at the example given this is what I need. Transpose through paste special only gives me 2 rows.

Collectives™ on Stack Overflow

Create Multiple Columns from Two Column Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related