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!
Solved! Go to 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,
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