Row-based data to columns

[jtensmeyer]

I am attempting to convert a set of that is in consecutive rows, i.e.:

Name: name1

data1: value1

data2: value 2

...

Name: Name2

data1: value1-2

data2: value2-2

..

Name: Name3

data1

etc

Into columns i.e.

Name          data1          data2

Name1        value1        value2

Name2        value1-2     value2-2

etc.

The examples I have seen all have some consistent value on rows that tie them together. My data is just ordered so that everything between "Name" belongs with that person. I have tried transform but can get it to not group everything together in a single row. Also worth noting: there are a lot of data rows (100 or so per name).

[DataNath · Accepted solution]

@jtensmeyer how does something like this look? The key here is to allocate an ID to each 'block' of records i.e. increase by 1 every time 'Name' comes back around. Then you can group the information that belongs to each ID when cross-tabbing the data. This is achieved by using a Multi-Row Formula where we basically say 'if the column = Name, give us a new ID, otherwise use the current one'.

Before:

After:

[jtensmeyer]

Thanks @DataNath that did the trick. I remember using that kind of approach in the past but had forgotten. Thanks for the reminder and the complete solution!