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
Résolu ! Accéder à la solution.
Hello @afv2688
I have one more requirements with the existing workflow. if you could help me with this , it will be of great assistance.
Requirement :-
I want that out of the Rejected one from New York , California & San Francisco data , latest rejected data should come into one another sheet name as "Rejected status" in the same workbook.
Latest Data means -Current date minus one.
Second requirement is that Product having "In Transmit" status from New York , California & San Francisco data should match and eliminated from "1st Level" sheet (i.e.which was already extracted as output data sheet name - 1st Level). Means "1st Level" sheet should not contain any "In transmit" product data .
All the supporting files are already attached in above example.
Please let me know if you have any doubts.
Regards
Rajeev
Hello @Rajeev18 ,
This solves the first requirement:
Could you explain a little better the second one? Couldn't really understand it.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
@afv2688
Thanks for taking time and assisting on it.
Let me explain it for you with the current output file. If you see in "1st level " sheet there are 3 product Item with ID 12345, 23456 & 234568 having 1st level Approval status as submitted and 2nd level approval status either submitted or Completed. But in sheet "New york" for the same product ID 1st level Approval status or 2nd level approval status is either rejected or in transmit or in issue.
If this is the case all the product which got rejected or may get rejected or in transmit or in issue in any country (New York,California, San Francisco) or in any level (1st level Approval status or 2nd level approval status) should get deleted from "1st level" sheet report.
Hence 1st Level sheet data should not include any product of rejected or may get rejected or in transmit or in issue. Considering this my 1st level sheet should not have any data as all the three items are either rejected or in transmit or in issue.
Data from 1st level sheet:-
Product ID | Product Name | 1st level Approval status | 2nd level Approval status |
12,345 | X | Submitted | Submitted |
23,456 | Y | Submitted | Completed |
234,568 | Q | Submitted | Submitted |
Hope I am able to make you understand.
Regards,
Hello @Rajeev18 ,
This wokflow will then eliminate from 1st status every project which is either in transit or rejected on the New York, San Francisco etc
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
@afv2688
I run the workflow, but i did not get the desired output I could still see all the data in "1st level" sheet which are in either in transmit, rejected status.
Could you please revisit the workflow . Also my send requirement was that all the latest rejected data should come into different sheet from both the column (1st level Approval status and 2nd level approval status), this also am not able to achieve. Could you please check again.
Regards,
@afv2688
In regards to first requirement, please note the details are as follows
Out of the three input file data and i want all the data in one sheet with the latest date data only (Means Today's date minus one i.e. yesterday's data) based on some condition. Could you please help on this.
I am attaching the latest files for your better understanding.
Condition are as follows.
Product which are in "Rejected", "May get rejected" or "In issue" status from all the three status ( 1st level Approval status, 2nd level Approval status & 3rd level Approval status) and from all the three files and which are of latest date data ( i.e. Today's date minus one i.e. yesterday's data) that should only come into output file (i.e. Output data should only contains data from yesterday's date from all the three files).
Input file1:
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date | 3rd level Approval status | Date |
345 | M | In Transmit | 12/13/19 | Submitted | 12/13/19 | Not Submitted | 12/27/19 |
389 | N | In Transmit | 10/19/19 | Submitted | 10/19/19 | Rejected | 01/17/20 |
145 | Z | Accepted | 12/13/19 | In Issue | 12/13/19 | Accepted | 01/12/20 |
278 | A | Accepted | 11/25/19 | Accepted | 11/25/19 | Accepted | 01/08/20 |
309 | B | Rejected | 12/13/19 | Rejected | 12/13/19 | Not Submitted | 12/27/19 |
207 | C | Rejected | 10/29/19 | May get Rejected | 10/29/19 | Rejected | 01/15/20 |
376 | W | Submitted | 12/11/19 | In Transmit | 12/11/19 | Rejected | 12/28/19 |
523 | X | Accepted | 12/11/19 | Accepted | 12/11/19 | Submitted | 12/23/19 |
412 | Y | Rejected | 12/01/19 | Submitted | 12/01/19 | Accepted | 01/01/20 |
809 | Z | May get Rejected | 12/11/19 | Accepted | 12/11/19 | May get rejected | 12/12/19 |
487 | Q | In Issue | 10/27/19 | Submitted | 10/27/19 | Accepted | 01/09/20 |
356 | s | Accepted | 12/13/19 | In Transmit | 12/13/19 | Submitted | 01/13/20 |
Input file 3:-
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date | 3rd level Approval status | Date |
131 | M | Submitted | 12/13/20 | Submitted | 12/13/19 | Not Submitted | 12/27/19 |
234 | N | Submitted | 10/19/19 | Submitted | 10/19/19 | Rejected | 01/17/20 |
456 | Z | Accepted | 12/13/19 | Not sent | 12/13/19 | Accepted | 01/12/20 |
789 | A | Accepted | 11/25/19 | Accepted | 11/25/19 | Accepted | 01/08/20 |
890 | B | Accepted | 01/17/20 | Rejected | 12/13/19 | Not Submitted | 12/27/19 |
567 | C | WIP | 10/29/19 | In Transmit | 10/29/19 | Rejected | 01/15/20 |
435 | W | Submitted | 12/11/19 | In Transmit | 12/11/19 | Rejected | 12/28/19 |
876 | X | Accepted | 12/11/19 | Accepted | 12/11/19 | Submitted | 12/23/19 |
213 | Y | Accepted | 01/12/20 | Submitted | 12/01/19 | Accepted | 01/01/20 |
345 | Z | Accepted | 12/11/19 | Accepted | 12/11/19 | May get rejected | 12/12/19 |
907 | Q | Accepted | 10/27/19 | Submitted | 10/27/19 | Accepted | 01/09/20 |
687 | s | Accepted | 12/13/19 | In Transmit | 12/13/19 | Submitted | 01/13/20 |
Input file 2:-
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date | 3rd level Approval status | Date |
12345 | q | In Transmit | 01/10/20 | Submitted | 12/13/19 | Rejected | 01/17/20 |
23456 | r | In Transmit | 01/16/20 | Submitted | 10/19/19 | Rejected | 01/17/20 |
98766 | t | Accepted | 12/13/19 | In Issue | 12/13/19 | Accepted | 01/12/20 |
45678 | e | Accepted | 11/25/19 | Accepted | 11/25/19 | Accepted | 01/08/20 |
32156 | f | Accepted | 12/13/19 | Rejected | 01/17/20 | Rejected | 12/27/19 |
23457 | g | Accepted | 10/29/19 | May get Rejected | 01/16/20 | Rejected | 01/16/20 |
45678 | e | Submitted | 12/11/19 | In Transmit | 12/11/19 | Rejected | 01/16/20 |
12980 | d | Accepted | 12/11/19 | Accepted | 12/11/19 | Submitted | 12/23/19 |
45987 | k | Accepted | 12/01/19 | Submitted | 12/01/19 | Accepted | 01/01/20 |
345678 | l | Accepted | 12/11/19 | Accepted | 12/11/19 | May get rejected | 12/12/19 |
234568 | u | In Transmit | 10/27/19 | Submitted | 10/27/19 | Accepted | 01/09/20 |
98765 | p | Accepted | 12/13/19 | In Transmit | 12/13/19 | Submitted | 01/13/20 |
Hope I am able to make you understand.
Regards,
Rajeev
Hello @Rajeev18 ,
Should be fixed now. I left one join in the middle that was just for checks.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
Hello @afv2688
I have accepted your solution as it was of great help and I am able to reach my goal to maximum extent, except for the two conditions .
1st Condition :- I want all the product which has "in transmit" or "Rejected" or "May get rejected" status from the output file "new York", "San Francisco" & "California" , all that should be removed from "1st Level" & "2nd Level" sheet.
2nd:- I want all latest one (i.e. Today's date minus 1, yesterday's date Data)"Rejected" or "May get rejected" status from the output file "new York", "San Francisco" & "California" should be appear in different sheet named as Rejected Status.
Hope I am able to explain you.
Please let me know if you need any additional details.
Regards,
Rajeev
Hello @Rajeev18,
Performed the changes as fullfilling your needs.
Tell me what you think, if there is something wrong tell me what the issue is.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
@afv2688 Thanks you for your patience. with this first condition getting full filled but not the 2nd one. Let me help you with more simple example.
This input data has all the data for all date including the yesterday's date. I want my output file to have only the yesterday's date data(i.e. for 2/25/2020) for rejected, or may get rejected status or "In Issue" status
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date | 3rd level Approval status | Date |
12345 | q | Rejected | 02/25/20 | Submitted | 12/13/19 | Rejected | 01/17/20 |
23456 | r | In Transmit | 01/16/20 | Submitted | 10/19/19 | Rejected | 01/17/20 |
98766 | t | Accepted | 12/13/19 | In Issue | 02/25/20 | Accepted | 01/12/20 |
45678 | e | Accepted | 11/25/19 | Accepted | 11/25/19 | Accepted | 01/08/20 |
32156 | f | Accepted | 12/13/19 | Rejected | 01/17/20 | Rejected | 02/23/20 |
23457 | g | Accepted | 10/29/19 | May get Rejected | 01/16/20 | Rejected | 02/25/20 |
345678 | l | Accepted | 12/11/19 | Accepted | 12/11/19 | May get rejected | 02/25/20 |
234568 | u | In Transmit | 02/25/20 | Submitted | 10/27/19 | Accepted | 01/09/20 |
98765 | p | Accepted | 12/13/19 | In Transmit | 12/13/19 | Submitted | 01/13/20 |
Output File should look like this.
Product ID | Product Name | 1st level Approval status | Date | 2nd level Approval status | Date | 3rd level Approval status | Date |
12345 | q | Rejected | 02/25/20 | Submitted | 12/13/19 | Rejected | 01/17/20 |
98766 | t | Accepted | 12/13/19 | In Issue | 02/25/20 | Accepted | 01/12/20 |