Matching Concatenated IDs to Individual IDs Between Two Files

[applegta]

Hello all,

I am trying to find a solution in Alteryx that I haven't been able to find already posted. I have two sets of files, we'll call them File A and File B. I am attempting to compare each file by Unique ID that is populated between the two files and the associated value that is with each Unique ID to ensure they match. However, File A, in some instances, has Unique ID's grouped on a single row [(Unique ID #1), (Unique ID #2), (Unique ID #3)...] with some spanning up to 15 Unique IDs in a single column. Example below:

File A:

[Unique ID 1]	5.50
[(Unique ID 2), (Unique ID 3), (Unique ID 4)]	324.75
[Unique ID 5]	65

File B:

[Unique ID 1]	5.50
[Unique ID 2]	150
[Unique ID 3]	75
[Unique ID 4]	99.75
[Unique ID 5]	65

I need to match against File A in aggregate, so I do not need to see the breakout between Unique ID's 2-4 but just need to know they match in total from File B to File A. How would I approach this?

Thank you!

[binuacs · Accepted solution]

@applegta you can split the data and do the join

[OllieClarke · Accepted solution]

Hey @applegta 

You can use the text to columns tool, along with some cleaning formulas to do this:

Hope that helps,

Ollie