Vlookup & Match

Hi,

I have to reconcile two sets of data based on a mapping file (see sample data set attached). Part of the problem is how irregular the data sets are in terms of spacing and formatting, and given the large volume of sheets that have to be reconciled, going in manually and changing the format is not ideal.

Initial thought process:

1. Create a key by combining sheet name with line number (ex. D1 L1)

2. Look up the value in each quarter with Vlookup & Match

3. Create a report that pulls D1 L1, D2 L1, and D2 L2, and compare if D1 L1 is equal to the sum of D2 L1 and D2 L2.

Questions:

1. How do I create a key by combining sheet name with values in a field?

2. Is there a Vlookup and Match substitute in Alteryx?

Appreciate any input/ideas.

Thanks!
Lucy

sample data.xlsx

Preparation

Accepted answers

Claje

1. Assuming that all sheets will have a Sheet and a Line value, you could Union these together for all 80 sheets, then join the unioned result together.
Alternatively, if the columns in the sheets line up, you could potentially bring them all in via a dynamic input tool, which could save you some time - I don't know how unstructured/different your data is - this option won't work if you really do have unique data cleanup that you have to do for each individual sheet.

2. If you use "Get Filename from Field" in the input data tool and select the "Full Path" option it will create a field called Filename that includes the Sheet name in it.
You should be able to use this for each input tool without issue.

For your unrelated question : It depends pretty heavily on the configuration that you have. You can specify a range on each sheet within the Data Input tool, you could use the Select Records tool to start your input at the line where "Line" is, and you could use the Select tool or the Dynamic Select tool in order to deselect the "noisy" fields that come before line.

Which of those methods will work best for you really comes down to just how complex everything is, and if this configuration is prone to change.

All comments

Claje

Hi,

Assuming that your data sets are consistent (D1 will always look similar to your example) you should be able to build this out using the Formula tool, alongside your input data tool. The input data tool has an option to include the file name as a field, and I believe you can incorporate the sheet name as well.

From there, to create a "key" you could write a formula like

[Sheetname]+[Field5]

To create a concatenated D1 L1 value.

I'd personally take each of your business rules and build a crosswalk in order to handle things like summing two lines on one side, something like this:

RuleNumber	LineNumber	SheetName
1	1	D2
1	2	D2
1	1	D1

Then you can join on Line Number and SheetName to your data to add that "Rule Number" field to all applicable spots.

Then you can Summarize, grouping by Rule Number, in order to sum up things like Lines 1+2.

From there, you can Join on RuleNumber between both datasets to create your match.

I'm not sure how new you are to Alteryx. If any of this is confusing, I can try and put together some screenshots to hopefully explain it better

After that you can Join on Line Number or Rule Number

fank3

Thank you for your suggestion. I am very new to Alteryx, but was able to follow your instructions.

A few follow up questions:

1. Adopting your rule # approach, is there a quick way to join the rules table to 80 sheets?

2. The expression [Sheetname] doesn't work, so I manually typed in "D1" for example to create a column for sheetname. Not sure why this is. Same as above, I have to append a sheet name column to 80 sheets, is there a quick way to do this?

One unrelated question: Is there a way to delete all the columns and rows to the left and above column "Line"?

Thanks again!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703