Search : in
By :

Excel Help: transpose cells by content

Last answer on Feb 21, 2009 8:51:30 am GMT colint, on Oct 27, 2008 3:36:37 pm GMT 
 Report this message to moderators

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

Configuration: Windows XP
Firefox 3.0.3

Best answers for « Excel Help: transpose cells by content » in :
Can vba return excel cell value with formats Show
Find a string in Excel and remove all cells Show
[Excel]Clear the contents of specific cells. Show [Excel]Clear the contents of specific cells Issue Solution Notes Issue I have 6 cells with dropdowns list and they are selected each time. I wish I could have a reset button for the last entry for 6 cells(to clear them) but of...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Import contacts from Excel to Outlook ShowImport contacts from Excel to Outlook Issue Solution Issue How to import a contact list from Excel to Outlook and having them in a single list of contact , and not as splited items? Solution By using the software Excel 2000 and...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

efi , on Oct 27, 2008 4:56:21 pm GMT

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.

   
Reply to efi

2

Prabul, on Dec 11, 2008 7:59:46 am GMT

I had the excatly the same problem.
Solved by this link
http://support.microsoft.com/kb/214024
Hope it helps

~Prabul

   
Reply to Prabul

3

 sunshinevivi, on Feb 21, 2009 8:51:30 am GMT

It works:
the formula from the microsoft page:

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

   
Reply to sunshinevivi
Ask your question Reply
They need your help