Hello
Please help me to find how to get 3 different output file with 3 different Input file in one excel workbook based on certain colour condition.
Example:-
File- 1
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date |
12345 | M | In Transmit | 12/13/19 | Submitted | 12/13/19 |
23456 | N | In Transmit | 10/19/19 | Submitted | 10/19/19 |
98766 | Z | Accepted | 12/13/19 | Not sent | 12/13/19 |
45678 | A | Accepted | 11/25/19 | Accepted | 11/25/19 |
32156 | B | Rejected | 12/13/19 | Rejected | 12/13/19 |
23457 | C | WIP | 10/29/19 | In Transmit | 10/29/19 |
45678 | W | Submitted | 12/11/19 | In Transmit | 12/11/19 |
12980 | X | Accepted | 12/11/19 | Accepted | 12/11/19 |
45987 | Y | Rejected | 12/01/19 | Submitted | 12/01/19 |
345678 | Z | May get Rejected | 12/11/19 | Accepted | 12/11/19 |
234568 | Q | In Issue | 10/27/19 | Submitted | 10/27/19 |
98765 | s | Accepted | 12/13/19 | In Transmit | 12/13/19 |
File 2: -
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date |
12345 | M | In Transmit | 12/13/19 | Submitted | 12/13/19 |
23456 | N | In Transmit | 10/19/19 | Submitted | 10/19/19 |
98766 | Z | Accepted | 12/13/19 | In Issue | 12/13/19 |
45678 | A | Accepted | 11/25/19 | Accepted | 11/25/19 |
32156 | B | Rejected | 12/13/19 | Rejected | 12/13/19 |
23457 | C | Rejected | 10/29/19 | May get Rejected | 10/29/19 |
45678 | W | Submitted | 12/11/19 | In Transmit | 12/11/19 |
12980 | X | Accepted | 12/11/19 | Accepted | 12/11/19 |
45987 | Y | Rejected | 12/01/19 | Submitted | 12/01/19 |
345678 | Z | May get Rejected | 12/11/19 | Accepted | 12/11/19 |
234568 | Q | In Issue | 10/27/19 | Submitted | 10/27/19 |
98765 | s | Accepted | 12/13/19 | In Transmit | 12/13/19 |
File 3:-
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date |
12345 | M | In Transmit | 12/13/19 | Submitted | 12/13/19 |
23456 | N | In Transmit | 10/19/19 | Submitted | 10/19/19 |
98766 | Z | Accepted | 12/13/19 | In Issue | 12/13/19 |
45678 | A | Accepted | 11/25/19 | Accepted | 11/25/19 |
32156 | B | Rejected | 12/13/19 | Rejected | 12/13/19 |
23457 | C | Rejected | 10/29/19 | May get Rejected | 10/29/19 |
45678 | W | Submitted | 12/11/19 | In Transmit | 12/11/19 |
12980 | X | Accepted | 12/11/19 | Accepted | 12/11/19 |
45987 | Y | Rejected | 12/01/19 | Submitted | 12/01/19 |
345678 | Z | May get Rejected | 12/11/19 | Accepted | 12/11/19 |
234568 | Q | In Issue | 10/27/19 | Submitted | 10/27/19 |
98765 | s | Accepted | 12/13/19 | In Transmit | 12/13/19 |
One OUTPUT File Require with different tabs-
Colour Condition as follow:-
In Transmit -Yellow
Accepted - Green
Rejected, May get Rejected & In Issue - Red
Solved! Go to Solution.
Hello @Rajeev18 ,
For that you need to use the table tool from the reporting tools. There you need to set custom rules for each column.
Since there are 3 possibilities you need to add 3 rules for each one.
After that, add a formula giving it a name to each reporting.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
Edit: added a sample
Hello @afv2688
Thank you for the reply. It is fulfilling the requirement for the shared example. However it creating some error with my existing flow chart where I wanted to implement it.
I am sharing the full example (attaching the excel as well) which I need to be executed. Kindly look into this and let me know if it can be implemented there. As am not able to combine two different formula in one workflow.
Thanks
Hello @afv2688
Input file 1(Raw Data): From this file I want output file (sheet named as submitted ) with data which should includes data of 1st level Approval status of submitted one and 2nd level Approval status of submitted and completed . Means submitted output file should not include any data of rejected, WIP, In issue, Not Sent.
Similarly From same Input File 1(Raw Data) :- one more output file (sheet names as Accepted) with data which should includes data of 1st level Approval status of Accepted and completed one and 2nd level Approval status of Accepted and completed.
Product ID | Product Name | 1st level Approval status | 2nd level Approval status |
12345 | X | Submitted | Submitted |
23456 | Y | Submitted | Completed |
98766 | Z | Accepted | Not sent |
45678 | A | Completed | Accepted |
32156 | B | Rejected | No Demand |
23457 | C | WIP | Not sent |
45678 | W | Submitted | In Issue |
12980 | X | Accepted | Accepted |
45987 | Y | Not sent | Submitted |
345678 | Z | Accepted | Accepted |
234568 | Q | Submitted | Submitted |
245677 | P | In Issue | WIP |
Output file 1 ( Sheet named Submitted ) will looks like this:
Product ID | Product Name | 1st level Approval status | 2nd level Approval status |
12345 | X | Submitted | Submitted |
23456 | Y | Submitted | Completed |
45987 | Y | Not Required | Submitted |
234568 | Q | Submitted | Submitted |
Output file 2 ( Sheet named Accepted ) will looks like this:
Product ID | Product Name | 1st level Approval status | 2nd level Approval status |
98766 | Z | Accepted | Not sent |
45678 | A | Completed | Accepted |
12980 | X | Accepted | Accepted |
345678 | Z | Accepted | Accepted |
Input File 2(New York): - This Input File should come exactly in same format in same output file named as New York with combination of few color formatting .
Like for status - "In transmit ' - Yellow Colour, "Accepted" - Green Colour, "Rejected", "In Issue" and "May get Rejected" - Red Color in both the approval Column.
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date |
12345 | M | In Transmit | 12/13/19 | Submitted | 12/13/19 |
23456 | N | In Transmit | 10/19/19 | Submitted | 10/19/19 |
98766 | Z | Accepted | 12/13/19 | Not sent | 12/13/19 |
45678 | A | Accepted | 11/25/19 | Accepted | 11/25/19 |
32156 | B | Rejected | 12/13/19 | Rejected | 12/13/19 |
23457 | C | WIP | 10/29/19 | In Transmit | 10/29/19 |
45678 | W | Submitted | 12/11/19 | In Transmit | 12/11/19 |
12980 | X | Accepted | 12/11/19 | Accepted | 12/11/19 |
45987 | Y | Rejected | 12/01/19 | Submitted | 12/01/19 |
345678 | Z | May get Rejected | 12/11/19 | Accepted | 12/11/19 |
234568 | Q | In Issue | 10/27/19 | Submitted | 10/27/19 |
98765 | s | Accepted | 12/13/19 | In Transmit | 12/13/19 |
Output File (New York) - should have this color combination: in same excel workbook under sheet named- New York
Similarly for Input file 3 ( California) & Input File 4 ( San Francisco) - Output file should have same colour combination ( as was done for New York) in the same workbook under sheet name - California & San Francisco .
In addition to that out of the Rejected one from output file New York , California & San Francisco data , latest rejected data (latest should count should based on Current minus 1 day) i.e. if for Today's date (12/13/19) Reject for 12/12/2019 should come into one another sheet name - "Rejected status" in the same workbook.
Another requirement is that "In Transmit" product status from New York , California & San Francisco data should eliminated from submitted sheet(i.e.which was already extracted as output data sheet name - Submitted). Means submitted sheet should not contain "In transmit" product data .
Hope I am able to make you understand what exactly I am looking for, I have attached all the file for reference .
Thank you in advance!
Regards,
Rajeev
Hello @afv2688
Thank you for taking time and helping with the this, Could you please help me with one more thing , I have one more workflow for different condition along with the one you shared. when i am clubbing both into one i am only getting the output sheet for your workflow only not for the existing one. I want all output to be in one excel workbook.
I have attached the complete workflow.
Please note earlier old workflow was giving output in that excel.
Regards,
Rajeev
Hello @Rajeev18 ,
You didn't attach any workflows.
To clarify, you want to have all sheets (sheet1, sheet2, sheet3) on the same workbook right?
Regards
Hello @afv2688
Yes your understanding is correct . I want all the sheets (sheet1, sheet2 , sheet3, sheet4 & sheet5) in one workbook.
I have attached the image of the workflow.
Do you need alteryx workflow as attachment?
Regards,
hello @afv2688
Here is the attached workflow and supporting file.
Regards,
Nice work @Rajeev18 ,
You almost got it right. You need to create also tables for sheet1 and 2
That would do it!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
Hello @afv2688 ,
Thank you so much for your time. It was of great help, now i am able to get the desired output.
Regards,
Rajeev