Vlookup in Alteryx through Find Replace Tool

[Gamaliel]

Hello, 

I would need someone's help on how to perform a vlookup in alteryx having the data sets below. I tried using find replace tool but it wont work in this case due to the format/order of the data below. I want to vlookup the amount from Trial Balance to General Ledger.

The problem is with the ordering of the words in the account name column.  I want a vlookup that will function even if the format or order of the words are not exactly the same with General Ledger file as long as there are identical words present then vlookup may be had.

General Ledger:

Trial Balance:

Observe at how the words are ordered or arranged. I could manually edit them but there are a lot of rows that is why it is inefficient to edit one by one.

Please see attached workflow.

Thank you.

[atcodedog05]

Hi @Gamaliel 

Can provide the expected output for the above scenario which is matched to which?

[Gamaliel]

@atcodedog05  this is not the output but this is how we connect them for vlookup identification:

The output would just be to add a column in the General Ledger for amounts coming from the Trial Balance, like this:

[atcodedog05 · Accepted solution]

Hi @Gamaliel 

I tried using fuzzy matching but it ended matching most of it to most of it. Unless we do a modification for each scenario (manual list would maybe much more faster to create) i dont see a way.

Let's see what others have to say.

[Gamaliel]

Thanks on this @atcodedog05 . I will wait for other's suggestions.

[Jean-Balteryx]

Hi @Gamaliel ,

I've tried exactly the same as @atcodedog05 with fuzzy matching but values are too close.

I feels like your values are concatenation, are they ?

[Gamaliel]

@Jean-Balteryx  Yes I concatenated the strings for General Ledger account name in order to be as closer to the TB account names. The account names for trial balance are different from the General ledger. When I concatenated them, more accounts were captured. It is just for these kind of isolated situations that find replace tool is not applicable.

[Jean-Balteryx]

If you have a reasonable amount of differences you could maintain them in a reference file that matches values from trial balance and general ledger that are different then you'd use it in Alteryx to replace them in the trial and make them match !