Vlookup in Alteryx through Find Replace Tool

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.

Find Replace Keywords.yxmd

Workflow

Accepted answers

atcodedog05

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.

All comments

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:

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 !

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703