Blending Data from different worksheets using a less complex workflow

[Ren]

Hi everyone! 

I'm new to Alteryx and I am having some issues with blending data for a school project. I tried out the tutorials and sought help from Alteryx videos on Youtube but nothing prepared me for the situation I am facing. 

Here's a basic breakdown of the situation: 

I have two .xlsx files (as attached) each containing multiple worksheets.
I am trying to blend the following

From File "MAS...2012", Worksheet AG1: Cells B4 - D4, B11, and B16 - D16
From File "MAS...2012", Worksheet AG2: Cells B4 - K4, B25 - K25, B30 - K30, B31 - K31,  B36 - K36, B37 - K37, B42 - K42, C67 - I67, C68 - I68, C74 - I74 
From File "Company Level Data...2012", Worksheet G4(Part 1): Cells B7 - B10, C7 - J7, C8 - J8, C9 - J9, C10 - J10

I am trying to achieve the exact presentation as set out in the file "Example". To do so, I created "Workflow 1". However, I was unable to achieve the presentation desired. Additionally, I do believe that the workflow is way too convoluted and complex,and a simplier one can be created. However, I am unsure as to how that can be achieved. 

Could I get some advice as to how I can achieve the exact presentation set out in "Example" using a simplier workflow? 

Thank You. 

[RodL · Accepted solution]

So the power of Alteryx comes from being able to take your initial spreadsheet (that is essentially an Excel "report") and get it into some form that is usable from an analytics standpoint (in other words, nicely ordered data structure). Attached is a quick example of how that can work. 

What you are trying to do is basically take the data from an Excel "report" and turn it into another Excel "report". If you are trying to get the output to look EXACTLY like your example with all of the formatting and positioning, that becomes difficult. This isn't really the "sweet spot" for using Alteryx...it CAN be done, but not without a lot of experimentation and time. My typical suggestion is to load the "raw" data (like what would come from the attached workflow) into a spreadsheet and then use Excel as your "reporting tool" (similar to using Tableau) and reference your data in the "raw data" tab (or a pivot table created from that raw data).

Here's a link to another post that discusses this...

http://community.alteryx.com/t5/Data-Sources/Creating-tables-when-outputting-to-Excel/m-p/20629#M146...

An added tip...take a look at Help for the Select Records tool. It can be good for trying to pull out non-contiguous data from an Excel report. I used it in the example, but did "hard code" the ending point.

[Ren]

Hi Rod,

Thank you for your reply and suggestions. 

If I understand correctly, I could work with the current workflow on Alteryx, and then export the data as an excel file, and then pivot table it to achieve the desired presentation?

Is there any way I can simplify the workflow to achieve the same output though? Would that be achieved by the using the "Select Records" tool thus eliminating the need for different "inputs" that reference the same worksheet?

Thank You once again!

 

[KaneG]

Hi Ren,

In answer to your question about the Select Records tool... Yes, you could use it in that fashion. However, maybe making it dynamic might be better and in this case is simple. Use a filter tool after your select with the condition simply being 

Left([Year],1) = "(" OR [Year]=="2012"

You can then split off the last row if you like for your totals using a sample tool.

Package attached

Kane