Addresses: 1 Row to Multiple Rows

[Mhynek1412]

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_1

ADDR_1

CITY_1

STATE_1

ZIP_1

LOCATION_2

ADDR_2

CITY_2

STATE_2

ZIP_2

LOCATION_3

ADDR_3

CITY_3

STATE_3

ZIP_3

V000123

1

PO BOX 88888

DALLAS

TX

75222-2689

2

575 WEST MURRAY BOULEVARD

ATLANTA

GA

30305

3

PO BOX 200715

DALLAS

TX

75320-0715

To

Vendor #	Location	Address	City	State	Zip
V000123	1	PO BOX 88888	DALLAS	TX	75222-2689
V000123	2	575 WEST MURRAY BOULEVARD	ATLANTA	GA	30305
V000123	3	PO BOX 200715	DALLAS	TX	75320-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 solution]

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 solution]

@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