# Solved Converting row data fields to column data in excel January 23, 2012 at 06:27:34
Specs: Windows XP
 I have some date extracted from running a script:A: 1234B: 2345C: 3456A: 5678B: 6789C: 7890A: 2345B: 4567C: 6789I want to get it converted into table format:A B C1234 2345 34565678 6789 78902345 4567 6789Appreciate your response. See More: Converting row data fields to column data in excel January 25, 2012 at 16:38:12
 Here's a way to do it with formulas. This will work for any number of headings, you'll just have to adjust the "helper column" formula, which will be explained below.The formulas suggested are based on the example data that you posted, but as long as you grasp the concept, you should be able to modify them to fit your exact situation.Let's assume your example data is in A1:A6.In B1, enter this formula and drag it across to D1:=LEFT(INDIRECT("A"&COLUMN()-1),FIND(":",INDIRECT("A"&COLUMN()-1))-1)In reality, you can drag it across as far as you need based on how many "headings" you have. Your example shows 3 headings, so you drag it across 3 cells.You should now have this:``` A B C D 1 Heading1: Value1 Heading1 Heading2 Heading3 ```Now we're going to create a "helper column". This column will be used to increment the formula below so that we can drag it around and make it update automatically.Based on your example, I will use Column E for the helper column since it is open. You can use any column you want, you'll just have to adjust the following formulas to match.In E1 enter 1In E2 enter =E1+3 and drag it down. Drag it down as far as you need so that the last number in the helper column is a bit higher than the last row of your data.The "3" is based on the number of headings you have. If you had 4 headings, the helper column would have to increment by 4, etc.Your example data has 6 rows of data, so I dragged it down until I saw the number 7.You should now have this:``` A B C D E 1 Heading1: Value1 Heading1 Heading2 Heading3 1 2 4 3 7 ```OK, last steps:Enter this in B2:=RIGHT(INDIRECT("A"&\$E1+COLUMN()-2),LEN(INDIRECT("A"&\$E1+COLUMN()-2))-(FIND(":",INDIRECT("A"&\$E1+COLUMN()-2))+1))Drag it over to D2 and then drag B2:D2 down one row.You should now have this:``` A B C D E 1 Heading1: Value1 Heading1 Heading2 Heading3 1 2 Value1 Value2 Value3 4 3 ValueA ValueB ValueC 7 ``` I assume that your real data is a lot longer than 2 sets, so simply drag the formula down as far as you need to in order to pick up all of your data. Finally, select all of the "transposed data" and perform a Copy...PasteSpecial...Values" to eliminate the formulas and lock in the values.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#1 January 23, 2012 at 06:54:52
 Your example data doesn't make sense to me.What does the vertical listing of repeated columns headings in the top section of your post mean?A:B:C:A:B:C:A:B:C:A, B and C are Column labels, yet you have them listed vertically as if they were rows.Are the A, B and C actually part of the data?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
January 23, 2012 at 21:19:55
 A:, B:, C: are part of row data. Consider them as headings.

Report •

#3 January 24, 2012 at 06:00:14
 Is there really a blank row between each set of 3 data rows?I guess I should ask the bigger question:How close to your real spreadsheet layout is the example data that you posted?If we write a VBA macro that works for the example you posted, do you know if it will work with your real data? Would you know how to modify the code if need be?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
January 24, 2012 at 21:36:03

Report •

#5 January 25, 2012 at 16:38:12
 Here's a way to do it with formulas. This will work for any number of headings, you'll just have to adjust the "helper column" formula, which will be explained below.The formulas suggested are based on the example data that you posted, but as long as you grasp the concept, you should be able to modify them to fit your exact situation.Let's assume your example data is in A1:A6.In B1, enter this formula and drag it across to D1:=LEFT(INDIRECT("A"&COLUMN()-1),FIND(":",INDIRECT("A"&COLUMN()-1))-1)In reality, you can drag it across as far as you need based on how many "headings" you have. Your example shows 3 headings, so you drag it across 3 cells.You should now have this:``` A B C D 1 Heading1: Value1 Heading1 Heading2 Heading3 ```Now we're going to create a "helper column". This column will be used to increment the formula below so that we can drag it around and make it update automatically.Based on your example, I will use Column E for the helper column since it is open. You can use any column you want, you'll just have to adjust the following formulas to match.In E1 enter 1In E2 enter =E1+3 and drag it down. Drag it down as far as you need so that the last number in the helper column is a bit higher than the last row of your data.The "3" is based on the number of headings you have. If you had 4 headings, the helper column would have to increment by 4, etc.Your example data has 6 rows of data, so I dragged it down until I saw the number 7.You should now have this:``` A B C D E 1 Heading1: Value1 Heading1 Heading2 Heading3 1 2 4 3 7 ```OK, last steps:Enter this in B2:=RIGHT(INDIRECT("A"&\$E1+COLUMN()-2),LEN(INDIRECT("A"&\$E1+COLUMN()-2))-(FIND(":",INDIRECT("A"&\$E1+COLUMN()-2))+1))Drag it over to D2 and then drag B2:D2 down one row.You should now have this:``` A B C D E 1 Heading1: Value1 Heading1 Heading2 Heading3 1 2 Value1 Value2 Value3 4 3 ValueA ValueB ValueC 7 ``` I assume that your real data is a lot longer than 2 sets, so simply drag the formula down as far as you need to in order to pick up all of your data. Finally, select all of the "transposed data" and perform a Copy...PasteSpecial...Values" to eliminate the formulas and lock in the values.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code. 