Hi Guys,
I have a need to prepare some data.
I need to map data from key branches to a target branch ID (the target needs to have this data registered against them).
The problem is that the column containing the source branch(es) is surrounded in useless text that I need to do away with.
An example of this is attached, however for this I'll show below:
target | source branch
2673 ---- :branch_code:
:target_branch_code:
- '12'
- '402'
:target_retailer_id: 1528
What I need is a column that gives me the target_branch_code numbers. Everything else can be removed.
How would I go about doing this, so that the output would be:
target | source branch
2673 12,402
Cheers!
Solved! Go to Solution.
How about the attached?
- Filter to remove Null record from sample file
- Text to Columns to split "source branch" on "-", space, or new line. It will ignore delimiters inside single quotes, and skip empty values (when there are consecutive delimiters)
- Multi-Row Formula to add the field "Field" because the field name and data are on different rows, this will fill the field name down the records
- Filter to remove the field name records, as they contain no data
- Multi Filed Formula to trim the single quotes and colons around the values
- Cross Tab to reshape and concatenate the data, resulting in one record per "target"
Thanks Joe, this worked perfectly!!
Alex