Challenge #19: Excel Record Locator

[pdave87]

Here we go!

Basics concepts always make foundation stronger. This was a very realistic exercise in terms of how structured data is consumed to process desired outputs. Awed by what Alteryx can do 🙂

Thanks to all SMEs for providing these challenges. Steps for review - 1) Import all using (*.xls) as absolute or relative file path 2) use attached tool (add to your macro list to make it a tool) and review the desired output for 2 or more than 2 files 🙂

Happy solving!

Regards,

Pratik

[OlehNikitenko]

Solution

[ainderike]

Find my solution attached. It was more difficult than I expected. Curious to see your solution 🙂

[OliverHosking]

Hello,

Here is my solution

[juliabarale002]

[drexelchin]

My solution

[jonathanwillis]

Here's my solution

[AndrewPowell]

Spoiler

So I loaded sheets and used the first row as the headings (assumption they are the same headings for each file)
created a row number using the IIF function to test if the filename was the same as the previous row in which case add 1 otherwise must be first row so start from 2 since the headings were row 1
formulae to change the unnecessary row values to Null(); could equally be zero but nulls are less space
select only the columns needed and rename for clarity
summarise by the file name using the max function

[EFos]

Spoiler

[YuriTchong]

Wow my solution is completely different after the file loading.