Separate data into groups based on conditions

[MinhTa]

Hello,

I am trying to create a system that gets an input file containing conditions and a database file and the workflow groups the database based on the conditions file. The database and condition is shown below. The database contains different file names and customers (in the sample, we have 2 files Sample1.csv and Sample2.csv). The condition file contains the members of each group. For example, in the file sample1.csv, we have 3 groups: group1 contains customer A and B, group2 contains customers C and D, and group3 contains the remaining customer left in the sample1.csv (which is customer E and F). For sample2.csv, we have 2 groups: group1 contains customer G and H and group2 contains the remaining customers in Sample2.csv (which is customer M). The output of the sample is displayed below, whenever a new group is formed, they have headers File Name and Customer1. I would like the workflow able to adapt to the changes in the condition file, just simple changes . For example if I add a new condition column called Group3 that contains E, after I run the workflow, it will form a new group for E. The output after adding Group3 is shown below.

Thank you.

Condition image:

Database image

Output:

Output of Sample1.csv after adding group3 to condition file:

Here are the sample database and condition:

[atcodedog05 · Accepted solution]

Hi @MinhTa 

Here is how you can do it.

Workflow:

Uppers branch

1. Using transpose tool to convert group columns to rows.

2. Using text to col to splits groups range into separate rows.

3. Filtering empty rows and data cleansing.

Lower branch

1. Using find & replace tool mapping group based on customers.

2. Using multi-row formula to flag start of new group.

3. Using gen row generating a row when new group starts.

4. Using formula tool to replace newly created row to "customer1". This way separator row is added.

5. Using select tool to keep only required columns.

Hope this helps : )