Challenge #19: Excel Record Locator

[Hatchetman]

Here are 2 solutions.

The first is a bit of a cheat as I did not 100% follow the instructions and used two Input tools but is very scalable if we only need to bring in two ranges on the same sheet.  If the number of ranges per sheet increased dramatically, not so scalable.

The second using only one Input tool I struggled with.  I was able to get there eventually but the solution is not as easily scalable with additional cell ranges. The provided solution is a much more elegant and scalable way to get these regardless of the number of files or ranges.  I didn't even thing of using the   Multirow formula to give each file's data a recurring row count that could be lined up to pull specific row info from no matter how many files were imported, very clever.

I am really enjoying learning how different people solve a problem with sometimes very different approaches and tools to get there.

[harrame]

Spoiler

Took a couple tries to find an elegant solution but I think I did okay. Learned some stuff about querying Excel!

Edit - looking through everyone else's solutions and it looks like I have a lot more to learn about excel haha

[cyg3003]

 I have no idea how to do so I check the solution workflow and then finished my workflow.

[padraigfmac]

I split the problem into two parts, one column at a time and then combined the outcomes are the end.

Spoiler

[josephmzhang]

Spoiler

[AnnaELawrence]

Spoiler

[tip77]

:|

[lucysinclair62]

My Solution Below! Might try it with a directory next time :) used more tools than other people but still works!

Spoiler

[sandeep4076]

Solution

[jbichachi003]

My solution!

Spoiler

Challenge 19 solution