Lookup for attendance to return who is eligible for bonus.

[VintageDan]

Hi all,

I am looking to create a formula that will look up a list of employees and check to see if they have had any sickness incidents within a month. This will then show eligible or ineligible for their name.

I have tried various IF and VLOOKUP methods, and that is where my knowledge ends. Having to manually complete at the moment with conditional formatting!

Can someone help please?

 

[etaf]

a countifs() may work
how do you know the date

what are you using in conditional formatting

what are the possible entries for sickness - you have red and orange highlighted

something like
COUNTIFS( RANGE of employes , employee to look for , range of codes , "*"&"SICK"&"*")

or you can , include in a list of employees

Book11
	A	B	C	D	E	F
1
2		name1	Working		name1	0
3		name2	Working		name2	1
4		name3	Working		name3	0
5		name1	Working
6		name2	sick
7		name3	Working
8		name1	Working
9		name2	Working
10		name3	Working
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(B2:B10)
F2:F4	F2	=COUNTIFS(B2:B10,E2,C2:C10,"*"&"sick"&"*")
Dynamic array formulas.

OR combined with an IF()

Book11
	A	B	C	D	E	F
1
2		name1	Working		name1
3		name2	Working		name2	Off Sick
4		name3	Working		name3
5		name1	Working
6		name2	sick
7		name3	Working
8		name1	Working
9		name2	Working
10		name3	Working
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(B2:B10)
F2:F4	F2	=IF(COUNTIFS(B2:B10,E2,C2:C10,"*"&"sick"&"*")>0,"Off Sick","")
Dynamic array formulas.

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[VintageDan]

Thank you I will try these. Apologies for the photo company restrictions on my laptop would not allow me to add file. I will look to see if I can share the data with you.

The range is looking at a whole month (April) and it needs to show if an employee has a sickness or unauthorized absence then they are ineligible.

Thanks for taking the time to help. Really is appreciated

 

[etaf]

the countifs() will work
But just need all the possible codes for
sickness or unauthorized absence

so you could add a couple of countifs() together , if there is no common part of the code

no dates then in the file - the data is only for a month anyway
so a countif() would work

=countif(range, "sickness or what ever the text is")+countif(range, "unauthorized absence or what ever the text is")

 

[VintageDan]

a countifs() may work
how do you know the date

what are you using in conditional formatting

what are the possible entries for sickness - you have red and orange highlighted

something like
COUNTIFS( RANGE of employes , employee to look for , range of codes , "*"&"SICK"&"*")

or you can , include in a list of employees

Book11
	A	B	C	D	E	F
1
2		name1	Working		name1	0
3		name2	Working		name2	1
4		name3	Working		name3	0
5		name1	Working
6		name2	sick
7		name3	Working
8		name1	Working
9		name2	Working
10		name3	Working
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(B2:B10)
F2:F4	F2	=COUNTIFS(B2:B10,E2,C2:C10,"*"&"sick"&"*")
Dynamic array formulas.

OR combined with an IF()

Book11
	A	B	C	D	E	F
1
2		name1	Working		name1
3		name2	Working		name2	Off Sick
4		name3	Working		name3
5		name1	Working
6		name2	sick
7		name3	Working
8		name1	Working
9		name2	Working
10		name3	Working
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(B2:B10)
F2:F4	F2	=IF(COUNTIFS(B2:B10,E2,C2:C10,"*"&"sick"&"*")>0,"Off Sick","")
Dynamic array formulas.

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

I have created a google worksheet and set to share via this link

the countifs() will work
But just need all the possible codes for
sickness or unauthorized absence

so you could add a couple of countifs() together , if there is no common part of the code

no dates then in the file - the data is only for a month anyway
so a countif() would work

=countif(range, "sickness or what ever the text is")+countif(range, "unauthorized absence or what ever the text is")

Thank you. Here is a link to the doc Attendance Bonus I have changed the names to unique ID's

I will try the above suggestion.

 

[VintageDan]

I have created a google worksheet and set to share via this link

Thank you. Here is a link to the doc Attendance Bonus I have changed the names to unique ID's

I will try the above suggestion.

The following is working =IF(COUNTIFS(B$1:B$1000,B3,C$1:C$1000,"*"&"sick"&"*")>0,"Not Eligible","Eligible") how can I add in the following conditions for additonal range:

Unauthorized, Maternity, Dependents, No Call No Show.

Thanks for your help so far. You have been amazing
Dan

 

[etaf]

oh sorry , i was working on a list solution for you and didnt post dah!!!

=UNIQUE(A1:A1000)

pulls across all the unique ID - note its a 2021/365 function , you have a list of different versions

then this will look in the list of codes in F2 to F9 and then count if they exist in the range B

=SUMPRODUCT(--(COUNTIFS($A$1:$A$1000,D2,$B$1:$B$1000,$F$2:$F$9)>0))

so all you need to do is add to that list - but also change the range in the F range of sumproduct

i notice it has in f2:f9 the same text twice - so hence why you are getting a count of 2
200380089

UK Sick Unpaid
UK Sick Partial Unpaid
UK Statutory Sick Pay
UK Unauthorized Absence Unpaid
UK Maternity
UK Dependents Leave
UK Sick Partial Unpaid
UK No Call No Show

Attendance Bonus .xlsx
	A	B	C	D	E	F
1	ID:	Pay Code		ID:		Ineligible reasons = RED
2	200614843	UK FC Vacation VTO Paid		200614843	0	UK Sick Unpaid
3	200614843	UK Night Shift Premium		111455197	1	UK Sick Partial Unpaid
4	200614843	UK Overtime		200821756	0	UK Statutory Sick Pay
5	200614843	UK Overtime Double		200557548	0	UK Unauthorized Absence Unpaid
6	200614843	UK Scheduled Work		200772935	0	UK Maternity
7	200614843	UK Vacation Paid		200395194	0	UK Dependents Leave
8	111455197	UK Carryover Leave		200380089	2	UK Sick Partial Unpaid
9	111455197	UK Night Shift Premium		111662684	0	UK No Call No Show
10	111455197	UK Overtime		112851327	0
11	111455197	UK Overtime Double		107930681	0
12	111455197	UK Scheduled Work		200380057	0
13	111455197	UK Sick Unpaid		200361604	0
14	200821756	UK FC Vacation VTO Paid		113038046	0
15	200821756	UK Night Shift Premium		200762087	0
16	200821756	UK Overtime		108179301	0
17	200821756	UK Overtime Double		110047112	1
18	200821756	UK Scheduled Work		200570627	0
19	200821756	UK Vacation Paid		110954259	1
20	200557548	UK FC Vacation VTO Paid		111947071	0
21	200557548	UK Scheduled Work		111306680	3
22	200557548	UK Voluntary Time Off		201297405	0
Sheet1

Cell Formulas
Range	Formula
D1:D197	D1	=UNIQUE(A1:A1000)
E2:E22	E2	=SUMPRODUCT(--(COUNTIFS($A$1:$A$1000,D2,$B$1:$B$1000,$F$2:$F$9)>0))
Dynamic array formulas.

dropbox file - only available for a few days

Dropbox

www.dropbox.com

 

[VintageDan]

oh sorry , i was working on a list solution for you and didnt post dah!!!

=UNIQUE(A1:A1000)

pulls across all the unique ID - note its a 2021/365 function , you have a list of different versions

then this will look in the list of codes in F2 to F9 and then count if they exist in the range B

=SUMPRODUCT(--(COUNTIFS($A$1:$A$1000,D2,$B$1:$B$1000,$F$2:$F$9)>0))

so all you need to do is add to that list - but also change the range in the F range of sumproduct

i notice it has in f2:f9 the same text twice - so hence why you are getting a count of 2
200380089

UK Sick Unpaid
UK Sick Partial Unpaid
UK Statutory Sick Pay
UK Unauthorized Absence Unpaid
UK Maternity
UK Dependents Leave
UK Sick Partial Unpaid
UK No Call No Show

Attendance Bonus .xlsx
	A	B	C	D	E	F
1	ID:	Pay Code		ID:		Ineligible reasons = RED
2	200614843	UK FC Vacation VTO Paid		200614843	0	UK Sick Unpaid
3	200614843	UK Night Shift Premium		111455197	1	UK Sick Partial Unpaid
4	200614843	UK Overtime		200821756	0	UK Statutory Sick Pay
5	200614843	UK Overtime Double		200557548	0	UK Unauthorized Absence Unpaid
6	200614843	UK Scheduled Work		200772935	0	UK Maternity
7	200614843	UK Vacation Paid		200395194	0	UK Dependents Leave
8	111455197	UK Carryover Leave		200380089	2	UK Sick Partial Unpaid
9	111455197	UK Night Shift Premium		111662684	0	UK No Call No Show
10	111455197	UK Overtime		112851327	0
11	111455197	UK Overtime Double		107930681	0
12	111455197	UK Scheduled Work		200380057	0
13	111455197	UK Sick Unpaid		200361604	0
14	200821756	UK FC Vacation VTO Paid		113038046	0
15	200821756	UK Night Shift Premium		200762087	0
16	200821756	UK Overtime		108179301	0
17	200821756	UK Overtime Double		110047112	1
18	200821756	UK Scheduled Work		200570627	0
19	200821756	UK Vacation Paid		110954259	1
20	200557548	UK FC Vacation VTO Paid		111947071	0
21	200557548	UK Scheduled Work		111306680	3
22	200557548	UK Voluntary Time Off		201297405	0
Sheet1

Cell Formulas
Range	Formula
D1:D197	D1	=UNIQUE(A1:A1000)
E2:E22	E2	=SUMPRODUCT(--(COUNTIFS($A$1:$A$1000,D2,$B$1:$B$1000,$F$2:$F$9)>0))
Dynamic array formulas.

dropbox file - only available for a few days

Dropbox

www.dropbox.com

Amazing thank you

 

[etaf]

you are welcome