Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

If Value Detected In Range Of Cells (Excel), Place Record On Separate Tab

robertfishel
8 - Asteroid

Alteryx Community, happy Wednesday!!

 

So I have a situation that I am not quite sure how to handle using Alteryx.  I have a data set (see pic A) that has a column (Column E) with values delimited with commas.  My next step is to find/replace the letters d and f with nothing.  Then, I break out each value to it's own column (pic B).  

 

What I would like to happen next is, for each record, I want Alteryx to look in the range of F through H, and if for example letter 'a' is detected, move that entire record to a separate sheet labeled a.  If there is a letter 'b', put the same record on a sheet labeled 'b', and so on.

 

pic A

1.PNG

 

pic B

2.PNG

 

pic C

3.PNG

 

pic D (tab 'a')

4.PNG

 

pic E (tab 'b')

5.PNG

 

pic F (tab 'c')

6.PNG

 

 

4 REPLIES 4
CharlieS
17 - Castor
17 - Castor

Since you want a record to show on each sheet, you need to make a record for each argument in the "Stuff" field. I used a Text-to-Columns (configured to rows) to achieve this. After that, a Formula tool can create the field used by the Output tool to "Take File/Table Name From Field". The Join was used to the original contents of the "Stuff" field was output with each record.

 

EDIT: Updated with filter for 'd' and 'f'. Nice catch, @NicoleJohnson.

danilang
19 - Altair
19 - Altair

Hi @robertfishel

 

Before step B, create a new field [SheetName] without the D and F.  instead of breaking this new field into columns, split to rows.  This will give you 3 rows for John Doe with "a", "b" and "c" in the SheetName column.  Use this column as the sheetname in your output tool like this

 

output tool.png

 

Dan

NicoleJohnson
ACE Emeritus
ACE Emeritus

Similar to @CharlieS and @danilang suggestions, but including a filter to remove those tabs that contain d & f :)

 

StuffExample.JPG

 

Three solutions for the price of one!!

 

Cheers,

NJ

robertfishel
8 - Asteroid

CharlieS, danilang, NicoleJohnson, thank you all!!!! Woo hoo!!!

Labels