Populating Result Based on Criteria

[kumara_faith]

Hi,

I have the data in Table 1 below which shows employee numbers by month:

Book1
	C	D	E	F	G	H	I
1	Table 1
2	Dec	Jan	Feb	Mar	Apr	May	Jun
3	123457	123457	123457	123457	123457	123457	123457
4	123458	123458	123458	123458	123458	123458	123458
5	123459	123459	123459	123459	123459	123459	123459
6	123460	123460	123460	123460	123460	123460	123460
7	123461	123461	123461	123461	123461	123461	123461
8	123462	123462	123462	123462	123462	123462	123462
9	123463	123463	123463	123463	123463	123463	123463
10	123464	123464	123464	123464	123464	123464	123464
11	123465	123465	123465	123465	123465	123465	123465
12	123466	123466	123466	123466	123466	123466	123466
13	123467	123467	123467	123467	123467	123467	123467
14	123468	123468	123468	123468	123468	123468	123468
15	123469	123469	123469	123469	123469	123469	123469
16	123470	123470	123470	123470	123470	123470	123470
17	123471	123471	123471	123471	123471	123471	123471
18	123472	123472	123472	123472	123472	123472
19	123473	123473	123473	123473	123473		123473
20	123474	123474	123474	123474		123474	123474
21	123475	123475	123475		123475	123475	123475
22	123476	123476		123476	123476	123476	123476
23	123477		123477	123477	123477	123477	123477
24		123478	123478	123478	123478	123478	123478
25						123479
Sheet1

In Table 2 below, I am trying to populate the employee numbers that left the company with a minus sign at the front of employee ID and employee that joined the company without a minus sign at the front of the ID. The correct result is as below:

Book1
	M	N	O	P	Q	R
1	Table 2
2	Jan	Feb	Mar	Apr	May	Jun
3	-123477	-123476	-123475	-123474	-123473	-123472
4		123478			123479
5
6
Sheet1

Is there a formula that I could use for the above ? Appreciate all the help. Thanks

 

[Anonymous1378]

I don't understand why you would record the new joiner for January in February, while recording the new joiner for May in May?

Otherwise, try

=CHOOSE({1;2},IFERROR(-FILTER(C3:C25,(COUNTIFS(D3:D25,C3:C25)=0)*(C3:C25<>""),""),""),FILTER(D3:D25,(COUNTIFS(C3:C25,D3:D25)=0)*(D3:D25<>"")*(COUNTIFS(B3:B25,D3:D25)=0),""))

in M3.

 

[kumara_faith]

Hi Anonynous,

My apologies. You are correct and the new joiner or resigned staff should be recognized in each month. I forgot to mention one other criteria which is that the new joiner should not repeated in the results. I added 123479 for the month of Jan and realized that the formula shows 123479 for Jan and also for May. Is there any way to ensure the new joiner is not repeated ? Appreciate all the help.

Book1
	C	D	E	F	G	H	I
1	Table 1
2	Dec	Jan	Feb	Mar	Apr	May	Jun
3	123457	123457	123457	123457	123457	123457	123457
4	123458	123458	123458	123458	123458	123458	123458
5	123459	123459	123459	123459	123459	123459	123459
6	123460	123460	123460	123460	123460	123460	123460
7	123461	123461	123461	123461	123461	123461	123461
8	123462	123462	123462	123462	123462	123462	123462
9	123463	123463	123463	123463	123463	123463	123463
10	123464	123464	123464	123464	123464	123464	123464
11	123465	123465	123465	123465	123465	123465	123465
12	123466	123466	123466	123466	123466	123466	123466
13	123467	123467	123467	123467	123467	123467	123467
14	123468	123468	123468	123468	123468	123468	123468
15	123469	123469	123469	123469	123469	123469	123469
16	123470	123470	123470	123470	123470	123470	123470
17	123471	123471	123471	123471	123471	123471	123471
18	123472	123472	123472	123472	123472	123472
19	123473	123473	123473	123473	123473		123473
20	123474	123474	123474	123474		123474	123474
21	123475	123475	123475		123475	123475	123475
22	123476	123476		123476	123476	123476	123476
23	123477		123477	123477	123477	123477	123477
24		123478	123478	123478	123478	123478	123478
25		123479	123479			123479
Sheet1 (2)

Book1
	M	N	O	P	Q	R
1	Table 2
2	Jan	Feb	Mar	Apr	May	Jun
3	-123477	-123476	-123475	-123474	-123473	-123472
4	123479				123479
Sheet1 (2)

Cell Formulas
Range	Formula
M3:R4	M3	=CHOOSE({1;2},IFERROR(-FILTER(C3:C25,(COUNTIFS(D3:D25,C3:C25)=0)*(C3:C25<>""),""),""),FILTER(D3:D25,(COUNTIFS(C3:C25,D3:D25)=0)*(D3:D25<>"")*(COUNTIFS(B3:B25,D3:D25)=0),""))
Dynamic array formulas.

 

[kumara_faith]

Hi Anonymous,

Please ignore my earlier post. New joiner staff number will repeat on all months after they join until they resign as below. I realized that if 123478 and 123479 resigned and left in June 2023, the formula returns as N/A instead in June as below. In addition, 123478 is also not showing in the results for Jan.Appreciate your help:

Book1
	C	D	E	F	G	H	I
1	Table 1
2	Dec	Jan	Feb	Mar	Apr	May	Jun
3	123457	123457	123457	123457	123457	123457	123457
4	123458	123458	123458	123458	123458	123458	123458
5	123459	123459	123459	123459	123459	123459	123459
6	123460	123460	123460	123460	123460	123460	123460
7	123461	123461	123461	123461	123461	123461	123461
8	123462	123462	123462	123462	123462	123462	123462
9	123463	123463	123463	123463	123463	123463	123463
10	123464	123464	123464	123464	123464	123464	123464
11	123465	123465	123465	123465	123465	123465	123465
12	123466	123466	123466	123466	123466	123466	123466
13	123467	123467	123467	123467	123467	123467	123467
14	123468	123468	123468	123468	123468	123468	123468
15	123469	123469	123469	123469	123469	123469	123469
16	123470	123470	123470	123470	123470	123470	123470
17	123471	123471	123471	123471	123471	123471	123471
18	123472	123472	123472	123472	123472	123472
19	123473	123473	123473	123473	123473		123473
20	123474	123474	123474	123474		123474	123474
21	123475	123475	123475		123475	123475	123475
22	123476	123476		123476	123476	123476	123476
23	123477		123477	123477	123477	123477	123477
24		123478	123478	123478	123478	123478
25		123479	123479	123479	123479	123479
Sheet1 (2)

Book1
	M	N	O	P	Q	R
1	Table 2
2	Jan	Feb	Mar	Apr	May	Jun
3	-123477	-123476	-123475	-123474	-123473	-123472
4	123479
5						#N/A
Sheet1 (2)

Cell Formulas
Range	Formula
R3:R5,M3:Q4	M3	=CHOOSE({1;2},IFERROR(-FILTER(C3:C25,(COUNTIFS(D3:D25,C3:C25)=0)*(C3:C25<>""),""),""),FILTER(D3:D25,(COUNTIFS(C3:C25,D3:D25)=0)*(D3:D25<>"")*(COUNTIFS(B3:B25,D3:D25)=0),""))
Dynamic array formulas.

Correct Result:

Book1
	M	N	O	P	Q	R
1	Table 2
2	Jan	Feb	Mar	Apr	May	Jun
3	-123477	-123476	-123475	-123474	-123473	-123472
4	123478					-123478
5	123479					-123479
Sheet1 (3)

 

[Anonymous1378]

I didn't have access to 365 at the time of answering your question, so I used CHOOSE() as a substitute for VSTACK, which did not go that well. Try this:

=VSTACK(IFERROR(-FILTER(C3:C25,(COUNTIFS(D3:D25,C3:C25)=0)*(C3:C25<>""),""),""),FILTER(D3:D25,(COUNTIFS(C3:C25,D3:D25)=0)*(D3:D25<>"")*(COUNTIFS(B3:B25,D3:D25)=0),""))

 

[kumara_faith]

Hi Anonymous,

Thank you for your solution and that worked. Appreciate it and have a great day ahead.