Excel Help: transpose cells by content

Closed
colint - Oct 27, 2008 at 11:36 AM
 sunshinevivi - Feb 21, 2009 at 03:51 AM
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

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

Oct 27, 2008 at 12:56 PM
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.
0
It works:
the formula from the microsoft page:

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