excelexcel-formulatextms-officems-office-script

How to convert a text with data in series having field name on left to a table in Excel?

I need to convert a dataset in series to a table in Excel. The dataset has field names on the left side in order and each dataset is one after another vertically. I want to have a common primary row with field names on top and the fields data below it one after another. Please tell me how can I do this?

Example:

Dataset:

Name: Max
Age:23
Grade: A
Name: Paul
Age: 24
Grade: B
Name: John
Age:23
Grade:D

To table:

Name Age Grade
Max 23 A
Paul 24 B
John 23 D

I tried copying each field to the top row in workbook and copying each data one after another but it is taking long time and I want a quicker way to do this.

Solution

  • Try this formula: in cell E1

    =VSTACK(TRANSPOSE(A1:A3),WRAPROWS(B1:B9,3))

    enter image description here