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: 1234
B: 2345
C: 3456

A: 5678
B: 6789
C: 7890

A: 2345
B: 4567
C: 6789

I want to get it converted into table format:

A B C
1234 2345 3456
5678 6789 7890
2345 4567 6789

Appreciate your response.


See More: Converting row data fields to column data in excel

Report •


✔ Best Answer
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 1
In 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
The data is on the same lines as posted.
Heading1: Value1
Heading2: Value2
Heading3: Value3
Heading1: ValueA
Heading2: ValueB
Heading3: ValueC
...
...
You can ignore the blank lines.
To be converted as...
Heading1 Heading2 Heading3
Value1 Value2 Value3
ValueA ValueB ValueC
...
...

I don't have knowledge on VBA macro. Do tell me how to use it.
Thanks.


Report •

#5
January 25, 2012 at 16:38:12
✔ Best Answer
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 1
In 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.


Report •

#6
January 31, 2012 at 03:42:21
Thanks!! It worked.

Report •


Ask Question