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

Question

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

pic B

pic C

pic D (tab 'a')

pic E (tab 'b')

pic F (tab 'c')

CharlieS · Accepted Answer

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 · Accepted Answer

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

Dan

NicoleJohnson · Accepted Answer

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

Three solutions for the price of one!!

Cheers,

NJ