Turn fields into different columns

[zemariajudice]

Hello,

I have a dataset with the following characteristics:

Row #1: Transaction A - Location New York

Row #2: Transaction B - Location Seattle

Row #3: Transaction A - Location Washington

Row #4: Transaction B - Location Detroit

...

I would like my dataset to look as follows:

Row #1 - Transaction A - Location 1 New York - Location 2 Washington

Row #2 - Transaction B - Location 1 Seattle - Location 2 Detroit

Basically, I would like to move from two columns (Transaction and Location), to three columns (Transaction, Location 1 and Location 2). What is the easiest way to do this?

Thank you in advance for your help!

Best

[michael_treadwell · Accepted solution]

Use Multi-Row Formula tool to create a unique location number grouped by [Transaction].

Use the Formula tool to create the row headers.

Then use the Crosstab tool to spread the data.

[zemariajudice]

Thanks @michael_treadwell!

[zemariajudice]

Michael,

When I run your flow, I get as many columns as the number of different locations I have in the data. However, I would like to have as many columns as the number of times a transaction appears in the database (e.g. assuming the largest transaction repeats itself five times, five "location" columns would be created), even if the location is the same in all columns. 

Do you - or anyone reading this - see a way to solve my issue?

Thank you in advance

[SophiaF]

Hi @zemariajudice,

Could you possibly give an example of what you would like the output to look like?

Cheers,

[cbridges]

@SophiaF totally going down a rabbit trail, but you have the best avatar since Mark's rolling BB-8!