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

Question

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):

Thanks.

apathetichell · Answer

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.

The_Alternator · Answer

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 · Answer

This is one way...

multiple company contacts.yxmd