I have some date extracted from running a script: A: 1234

B: 2345

C: 3456A: 5678

B: 6789

C: 7890A: 2345

B: 4567

C: 6789I want to get it converted into table format:

A B C

1234 2345 3456

5678 6789 7890

2345 4567 6789Appreciate your response.

✔ 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 Heading3Now 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 7OK, 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.

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.

A:, B:, C: are part of row data. Consider them as headings.

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.

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.

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 Heading3Now 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 7OK, 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.

Thanks!! It worked.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History