Challenge #19: Excel Record Locator

For this week’s challenge we can look at using Alteryx to automate a repetitive task. The link to last week’s challenge (challenge #18) is HERE.

Use Case: Customer has 100's of xls files with 1 common sheet available in all workbooks. Through one process, the user would like to read across all of the xls files and return the values contained in specific cells - Row 2, Column 3 and Row 8, Column 2 for each sheet within each XLS workbook.

The result should be a table OR browse tool containing 3 columns: XLS File, Row2_ Column3, and Row8_Column2.

You will only have 2 xls files for this challenge, Book1 and Book2, but keep in mind that the use case is for 100s of Excel files with the same schema. You won’t want to use 2 input tools since that would not scale to 100’s. Also, for all data consumption, please check the box for First Row Contains Data. This is because in the headers for an Excel file are in row #1.

Good luck and keep it simple, this should be an easy challenge!

Book1.xls

Book2.xls

challenge_19_start_file.yxmd

challenge_19_solution.yxmd

Preparation

Data Analysis

Core

Transform

Basic

Accepted answers

All comments

TaraM

The soultion has been posted above.

Spoiler

2016-04-04 12_21_13-Alteryx Designer x64 - Week19_DataPrep_RecordLocator_Beginner_Solution.yxmd_.png

SeanAdams

Provided solution from @GeneR and @TaraM is more direct - attached mine below

Spoiler

- Added a row-ID using a multi-field formula (same as provided)
- Added a field that only held the value for the Row2 result, and a name column
- Same for the row 8 result, and a name column
- Filtered out all blanks
- Quick transpose
- job done...

Same outcome - just a different approach

challenge_19_SeanSolution.yxmd

MarqueeCrew

Yikes!

I missed the K.I.S.S. principle here.

challenge_19_MarqueeCrew.yxmd

NicoleJohnson

We actually used this one as a demo in a User Group meeting last year - just realized I already had the answer! Posted!

Spoiler

Solution: Tile (to get Record ID by XLS file name) > Transpose > Filter for Column 3/Row 2 (F3 & Tile # 2) and Column 2/Row 8 (F2 & Tile # 8) > Cross Tab

challenge_19_NicoleJohnson.yxmd

estherb47

Used a directory tool, dynamic input, and tile tool

challenge_19_EHB_solution.yxmd

Laurap1228

My solution was the same as the one provided.

Spoiler

challenge_19_LP.yxmd

LordNeilLord

Pretty much the same as the solution

Spoiler

mceleavey

Ok, going through the back catalogue.

This was a nice and easy one and I assume everybody has done this in pretty much the same way:

Spoiler

I started by setting the path in the input tool to use a * to replace the filename of the Excel sheet, then set the filename to be brought in as a column in the input tool using the "Output Filename as Field" option. Then I used the multi-row formula to assign a Row ID, restarting the numbering sequence at each change in filename.
I then used a formula to determine where it was Row ID 2 and Row ID 8, assigning the column names as the value, and assign a column to highlight these rows, and a value field to return the value of column 3 where the Row Id was 2 and column 2 where the Row ID was 8.
I then simply removed the extraneous rows and cross-tabbed the data accordingly.

Challenge_19_Solution_CM.yxmd

LordNeilLord

Workflow

challenge_19_LNL.yxmd

A_Twa

My solution attached; thanks!

challenge_19_start_file_AJT.yxmd

MsBindy

Yes, I can overcomplicate a ham sandwich. Got there, but with all the condiments.

challenge_19_MsBindy.yxmd

robcarroll

Solution.

challenge_19_solution.yxmd

DaveB

A different approach.

Exercise 19 solution.yxmc

ADerbak

Tried to do a macro and couldn't quite get it to work right, went with tiling and formulas instead.

Solution attached!

challenge_19_solution_AD.yxmd

nick_ceneviva

Solution attached!

challenge_19_Ceneviva.yxmd

jamielaird

Here's my solution.

Edit: And yes, having looked at others I'm kicking myself for not doing the selection with a single formula tool - doh!

Spoiler

challenge_19_JL.yxmd

patrick_digan

Spoiler

challenge_19_start_file.yxmd

Natasha

Looking at other solutions, I guess I could do without a transpose.

Spoiler

challenge_19_NK.yxmd

data_rachel

Spoiler

challenge_19_start_file.yxmd

marcreid

Took the long way round on this one..

Spoiler

challenge_19_MR.yxmd

hoheusle

Spoiler

I first used the record ID tool, but that doesn't work since you need to restart at 1 for a new file name. Within the Multi-Row Formula, this can be quickly done by grouping by FileName. Helps simplify the expression.

JoshKushner

A good, easy break

challenge_19_start_file.yxmd

dominiklz

Spoiler

challenge_19_Dominik.yxmd

930realm

Had to change the file format to xlsx because I kept getting an error trying to open the xls. I thought I would be able to use the sql to bring back the fields, but it looks like I can only bring in sheet names and named ranges. All in all it looks like my solution is similar to others.