Excel Help: transpose cells by contentClosed

Question

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 : &pound;0.85
5Break Price : &pound;0.72
6Break Quantity :200
7In Stock. 
8Qty :     
1DOGGY RUBBER
2DOGGY RUBBER
3Code: 10120
4Sale Price : &pound;0.21
5Break Price : &pound;0.16
6Break Quantity :1500
7In Stock. 
8Qty :     
1MINI FUNKY DES TAPE
2MINI FUNKY DES TAPE
3Code: 10125
4Sale Price : &pound;0.85
5Break Price : &pound;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

efi · Answer

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.

Prabul · Answer

I had the excatly the same problem.
Solved by this link
https://support.microsoft.com/en-us/office/how-to-convert-multiple-rows-and-columns-to-columns-and-rows-in-excel-09c017ec-a151-41b0-9caf-60b01f9a4deb?ui=en-us&rs=en-us&ad=us
Hope it helps

~Prabul

sunshinevivi · Answer

It works:
the formula from the microsoft page:

C1 forumula:
=OFFSET($A$1;(ROW()-1)*8+INT((COLUMN()-3));MOD(COLUMN()-3;1))

Excel Help: transpose cells by content

3 responses

Similar discussions

Subscribe To Our Newsletter!