Excel Help: transpose cells by content [Closed]

Report
-
 sunshinevivi -
Hello Forum,

I am struggling! I am trying to find a way to get a current column of data transposed into seperate columns of the same genre... Heres the list. Each of the rows numbered 1-8 is a single product.

1INFLATABLE POLITICAL GLOBE
2INFLATABLE POLITICAL GLOBE
3Code: 10002
4Sale Price : £0.85
5Break Price : £0.72
6Break Quantity :200
7In Stock.
8Qty :
1DOGGY RUBBER
2DOGGY RUBBER
3Code: 10120
4Sale Price : £0.21
5Break Price : £0.16
6Break Quantity :1500
7In Stock.
8Qty :
1MINI FUNKY DES TAPE
2MINI FUNKY DES TAPE
3Code: 10125
4Sale Price : £0.85
5Break Price : £0.72
6Break Quantity :160
7In Stock.
8Qty :

So what I need is for all `1`s to be in column B, all `2`s in column C, all the `3`s to be in column D and so on...

Is there a simple way to do this - there is about 28k rows of data, so too many to do by `hand`...

Thanks in advance.
Colin

3 replies

Posts
1
Registration date
Saturday October 25, 2008
Status
Member
Last seen
October 27, 2008

You can use the funcationality of Text to columns that is select the column and then go to Header as Data-->Text to column this will make the speration.

or you can use the funcationality of filter which will help u lot to elimate text according to your requirments.
It works:
the formula from the microsoft page:

C1 forumula:
=OFFSET($A$1;(ROW()-1)*8+INT((COLUMN()-3));MOD(COLUMN()-3;1))

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!