how to get colour one output file against multiple input file

[Rajeev18]

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

[Rajeev18]

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

[afv2688]

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

[Rajeev18]

@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,

[afv2688]

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

[Rajeev18]

@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,

[Rajeev18]

@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

[afv2688 · Accepted solution]

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

[Rajeev18]

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

[afv2688]

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

[Rajeev18]

@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