logic check guidance needed

Question

I have an Excel workbook with two sheets:

"Interface" – containing the columns: Emp_id, Variable_type, and Amount

"Variable" – containing the columns: User_id, Paycode, and Value

I need to perform a reconciliation between these two sheets by:

Concatenating the following columns to create unique keys:

In the Interface sheet: Emp_id, Variable_type, Amount

In the Variable sheet: User_id, Paycode, Value

Using these concatenated keys, perform a lookup to:

Identify entries that are present in Interface but missing in Variable

Identify entries that are present in Variable but missing in Interface

Finally, I want to create two new columns in both sheets to show the missing entries (i.e., unmatched records) from each side.

Approach i followed is : input both sheets> data cleaning by removing trailing spaces> formula tool :interface sheet

new column: emp_id clean: lowercase([emp_id)}

new column:Variable_type clean: lowercase([Variable_type)}

new column:amount clean: ltostring(round(tonumber(amount)},2))

new column: concat [emp_id clean]+[Variable_type clean] +[ amount clean]

did same for other sheet and then used join tool using concat_key

Is this correct or is there any other way which can tell the vlookup /missing entries in each sheet ?

reconciliation data.xlsx

jrlindem · Answer

Agree with @binu_acs but if you like to keep the pieces (transformations) separate you can do them one by one in the formula tool since we know the order of the formulas is regarded and each formula can reference the ones above it.  Then you can add in a Select Tool to remove out the incremental steps from flowing downstream.  Sometimes this makes for easier debugging.

Gaurav_Dhama_ · Answer

That is correct, that should give you correct result, however you don't really need to create a unique ID, as both the tables have same number of columns that you are trying to match and also each column if complete value.

Use data cleansing tool to clean your data, get rid of those extra spaces, upper/lower case your values.

Attached is the workflow doing the same.

I would create a unique ID, if the values in columns were not a direct match or if number of columns did not match, for example 2 columns in variable table and 3 columns in interface table.

If you want to use vlookup in excel to do this task, then you can definitely get the results with what you have mentioned, but then you will need to add vlookup to both the sheets. Sheet where you find rows with #NA as result, is the data that is missing in the other sheet.

logic check.yxmd

binu_acs · Answer

@Navya08 That is the correct approach, if you want to avoid the extra columns, you can do all the concatenation in one field

LowerCase(Trim(toSTring([Emp_id]))+Trim([Varibale_type])+Trim(ToString([amount],2)))