Addresses: 1 Row to Multiple Rows

Question

I have a source file that includes multiple remittance addresses for a single vendor. To put it very simply, I am looking for a workflow that will do the following:

Vendor #LOCATION_1ADDR_1CITY_1STATE_1ZIP_1LOCATION_2ADDR_2CITY_2STATE_2ZIP_2LOCATION_3ADDR_3CITY_3STATE_3ZIP_3V0001231PO BOX 88888DALLASTX75222-26892575 WEST MURRAY BOULEVARDATLANTAGA303053PO BOX 200715DALLASTX75320-0715

To

Vendor #LocationAddressCityStateZipV0001231PO BOX 88888DALLASTX75222-2689V0001232575 WEST MURRAY BOULEVARDATLANTAGA30305V0001233PO BOX 200715DALLASTX75320-0715

Not all of the vendors will have 3 addresses (some may have less and some may have more) it will depend on the LOCATION_X field in the source data where X = # of locations.

Thanks!

Thableaus · Accepted Answer

Hi @Mhynek1412

Here's a way to do this:

- Transpose all columns, except Vendor#

- Parse column "Name" - you will separate Columns and GroupID

- Cross-Tab to get column names where they need to be. You group by the ID field created.

- Use Select tool to deselect unnecessary columns, rename fields, or put them in order.

WF appended.

Cheers,

MarqueeCrew · Accepted Answer

@Mhynek1412,

Here's a dynamic approach:  Transpose the data, get rid of the underscore digits, calculate the location number on each row, get rid of the location (original) field, Cross Tab your data.

This will get you your desired results.

Cheers,

Mark