Creating new fields based on row values and collapsing into one row

[The_Alternator]

Okay so my data is essentially like this:  

Company | Contact Name | Phone Number | Address

Sam's Club | Richard Braun | 486-222-3425 | 215 Cedar Rd

Sam's Club | Mark Jackson | 212-908-7876 | 6452 Loan Oaks Rd

Costco | Jason Brown | 808-234-5489 | 5429 Park Circle

Costco | Stephanie Burg | 309-254-9090 | 8786 Bryant Lake Dr

So as you can see, Company is repeating, but the rest of the values are unique. So what I'm looking for is to transform the data to this (collapse to company level like this):

Company | Contact Name 1 | Contact Name 2 | Phone Number 1 | Phone Number 2 | Address 1 | Address 2 |

Sam's Club | Richard Braun | Mark Jackson | 486-222-3425 | 212-908-7876 | 215 Cedar Rd | 6452 Loan Oaks Rd

Costco | Jason Brown | Stephanie Burg | 808-234-5489 | 309-254-9090 | 5429 Park Circle | 8786 Bryant Lake Dr

Thanks.

[apathetichell]

This is one way...

[The_Alternator]

Sorry - I should've been more clear. The data is not all in one field like that. There is not pipe in the values, I simply put that there to separate the fields. Each value between the || has its own cell in the data. The Company, Contact Name, etc are the names of the columns. They are not values in the data.

[apathetichell]

The theory should be fine - if you have trouble adapting the workflow to your actual data - can you post something closer to your data?

Try cutting out the first three tools - attach your data to a recordid (mark as key column in the transpose and crosstabs) and give it a whirl with your data.