Transpose row data to columns

[dsemitekol]

Hello Community!

I know this is a popular question but after trolling the community it seems like other users have slightly more cleaned versions of what I am trying to accomplish.

So here it goes.  I have a list of people all in one column.  I want to transpose/convert the rows to columns.  Here is some sample data:

Bart Simpson

742 Evergreen Terrace

Springfield

Lisa Simpson

742 Evergreen Terrace

Springfield

Marge Simpson

742 Evergreen Terrace

Springfield

As you can see, the list follows a routine, so I feel like it is a simple formula that says, take every x number of rows and move to a new column?  So the end result would look like this:  (The pipes represent a new column)

Bart Simpson   |   742 Evergreen Terrace  |  Springfield

Lisa Simpson  |  742 Evergreen Terrace  |  Springfield

[Kenda · Accepted solution]

Hey @dsemitekol! I was able to get the output you are looking for with a couple Multi-Row Formula tools and a Cross Tab tool.

First, from your input, add a Multi-Row Formula tool and create a new field called NewField with the following expression:

iif([Row-1:NewField]=1,2,iif([Row-1:NewField]=2,3,1))

Then, add another Multi-Row Formula tool with another new field called NewField2 with this expression:

iif([NewField]=1,[Row-1:NewField2]+1,[Row-1:NewField2])

Finally, add a Cross Tab tool and group by NewField2. Hope this helps!

[dsemitekol]

Thanks @Kenda that works great!  I kind of see the logic in the formula but would you be able to include a couple of notes?

Thanks again!

[MarqueeCrew]

@dsemitekol,

Here is a slightly different solution:

First, I append the "FIELD Count" of X (in this case 3) that defines the rows per record to each record.  I did this so that dynamically, I can "hash" through each row and say:

If I add 1 to the last row count and am still less than the number of rows/record, then that is my field number.

Secondly, once I have 1/2/3 in this column naming completed I now look to see if my current field number is higher than my last.  If it is, then I'm on the same record number as the last record.  So for field 1 going to 2, I remain with 0.  When I am on a row where my field number was reset to 1, then the prior row is 3 (higher) and I now increment my record id.

Then I crosstab to go from rows of data to columns of data and use the recordid to group data, the fieldid to name the header/column and the original field data for the value.

Cheers,

Mark

[Kenda]

@dsemitekol I would be happy to!

For the first Multi-Row Formula, because you know that the data is sectioned by groups of three, you are assigning a value of 1 to the first row of the group, a 2 to the second row of the group, and a 3 to the third. You do this so that all of the 1's become the first column after cross tabbing, the 2's become the second column, and the 3's become the third column. You need the second Multi-Row Formula tool so that you can group by each person's details. Each time NewField is 1 (AKA a new person's section of rows), the NewField2 counter will increase by 1. By grouping by this field in the Cross Tab tool, each value of NewField2 gets its own row instead of being concatenated together.

[dsemitekol]

Thanks @Kenda

I am starting to see how the formula is constructed, but I'm still just a little confused.  Let's say I wanted to group in 4 instead of 3.

iif([Row-1:NewField]=1,2,iif([Row-1:NewField]=2,3,1))

I think what is really throwing me off is the first and second iif formula.  Why just the "1,2" in the first half and why in the second half is the order "2,3,1"  ?  The pseudo code makes sense: we are saying taking the first, second, and third rows and assign them a 1, 2, or 3 in the [NewField] column.  And I think the [Row-1] is just saying to look at the last row to decide which number to assign until it reaches 3, then start over and assign the next row a 1. 

Thanks again for the help!

[dsemitekol]

Thanks @MarqueeCrew

I see this solution works well also and I understand your logic to reach the end goal.  It seems like appending the "Row Count" also gives you the ability to change the groupings for when your source data changes.  For example, if my list contains a phone number too.

Thanks again for your help and for posting the workflow!

[MarqueeCrew]

@dsemitekol,

Yes!  I saw your question about the #3 and wondering what to do if the number was 4.  I wanted to externalize the count from the formula.  If the count was 100 fields per record, this can change by a parameter rather than code.  The input text file could be an excel file/sheet or it could be a question in a macro.  

Thanks for your post!

Cheers,

Mark

[Kenda]

@dsemitekol Your thinking is correct. In my own words, I would explain it like this... IF the previous row is 1, this row should be 2. ELSE IF the previous row is 2, this row should be 3. ELSE, make it 1.

I created it this way because I knew you had 3 rows per person. If you had 4, however, you could modify the last part of the formula as so:

iif([Row-1:NewField]=1,2,iif([Row-1:NewField]=2,3,iif([Row-1:NewField]=3,4,1)))

Otherwise, another formula that I just thought of and that is more robust could be like this:

iif([Row-1:NewField]=3,1,[Row-1:NewField]+1)

Where you just change what [Row-1:NewField] equals to the max number of rows for the group. Similar to @MarqueeCrew's approach but without the appended field.

[dsemitekol]

That makes sense!  Thanks again for the help @Kenda