Import worksheets (different schema) and sync/rename columns

[TomBock]

Hi:

I need some assistance with importing 3 worksheets (within same XLSX file) where the structure (schema) is slightly different across each worksheet.

I've searched across this forum and did find some existing solutions which *partially* helped me to import the worksheets.  

However, ultimately, I am not (yet) able to output the all data into the desired format (see attached JPG incl. "desired" and "current" output).

Subfolder "SourceFiles" incl. both a) source data and b) lookup table for renaming/sync. the columns.

I'd welcome any feedback which would output the six (6) records across 3 columns (Date, Deposit, AccountID).

Thank you in advance,

Tom

 

 

[binuacs · Accepted solution]

@TomBock Added find and replace tool in your workflow solved the issue

[TomBock · Accepted solution]

@binuacs -- thank you for the prompt response.  I appreciate it.

Once I downloaded the version and placed the source file into the subfolder AND then ran it, I only generate a single-record output.   See attached.

What am I missing?

 

[binuacs]

@TomBock the RecordID field should be selected in the cross tab tool, can you check?

[TomBock]

@binuacs -- that did the trick.   It wasn't checked... but now it's outputting the data as expected.   Many thanks!!

Cheers,

Tom

[TomBock]

@binuacs -- sorry, one more follow up question.

In "re-review" of your solution, could you please provide some info as to what exactly the logic for the Find/Replace tool is.  I would I never thought to us it in this case.   Just trying to learn from this challenge.   Thank you.

[binuacs]

@TomBock Find and Replace tool works very similarly to Excel Vlookup function. In your use case, the Name field will be looking against your look-up file and if find a match in the BEFORE field then the Name field will get replaced with the AFTER field in the lookup table

[TomBock]

@binuacs -- great -- your explanation helps out a great deal.   Again, thank you for the assistance w/ solving the challenge most efficiently.

Cheers,

Tom