Get the number of duplicates in each day on selected month

[akram-5]

Hi All
Hope everyone is having a good day.
I am posting here after a long time. I couldn't get this figured on my own.

i have a data sheet where employees are conducting different jobs recorded with dates and job details.
These employees gets an allowance based on number of days he completes a job. But NOT how many jobs he did on same day.
A statement is taken end of every month to calculate this allowance.
I have people working on this file from 6 different sites.
So make this calculation easier for them i would like to make a macro/userform where user selects a month and hits generate report and data is filtered by month and copied to another sheet, where the data is again filtered and days counted, where employee entries/jobs in the same day is counted only once.

basically result for Jan 2022 should look like

Date Name Number of Days
24/01/2021 James 2
24/01/2021 Elisa 2
24/01/2021 Dominik 2
24/01/2021 Martin 1


for example, you can notice that Dominik has worked on 3 jobs in this month, but he worked only 2 different days.

Hope you guys can help, the autofilter codes and loops i tried is ****. so i didnt add them here.

thank you.

 

[Peter_SSs]

I am posting here after a long time.

Two things that will help you get faster/better solution in the updated forum:

1. Update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2. We cannot copy your sample data from a picture like that but MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[akram-5]

Two things that will help you get faster/better solution in the updated forum:

1. Update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

View attachment 55917


2. We cannot copy your sample data from a picture like that but MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Peter, thanks. updated info as suggested.

Below here is a mini sheet of my data sheet

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	E1	T5	H8	GZ	S CT	Total Time	End time	Start Time	Name	Date	Report-No	#
3	1		1	W2323	KH	0:50	4:20	3:30	James	1/16/2022	RP-01	1
4		3		E42324	KM	0:50	4:20	3:30	Peter	1/17/2022	RP-02	2
5	4		1	W2324	KE	0:50	4:20	3:30	Parker	3/16/2022	RP-03	3
6		7		E42325	DT	0:50	4:20	3:30	Dominik	1/21/2022	RP-04	4
7	2	2	1	W2325	DT	0:50	4:20	3:30	Martin	2/20/2021	RP-05	5
8		6		E42326	KH	0:50	4:20	3:30	Dominik	1/21/2022	RP-06	6
9		3		W2329	KT	0:50	4:20	3:30	Elisa	1/22/2022	RP-07	7
10	8		1	E42327	DT	0:50	4:20	3:30	Iqbal	1/23/2022	RP-08	8
11		8		W2330	KE	0:50	4:20	3:30	James	1/24/2022	RP-09	9
12	3			E42328	KM	0:50	4:20	3:30	Dominik	1/25/2022	RP-10	10
13		1		W2331	KH	0:50	4:20	3:30	Martin	1/25/2022	RP-11	11
14	7	9		E42329	DT	0:50	4:20	3:30	Thomas	2/27/2022	RP-12	12
15			1	W2332	DT	0:50	4:20	3:30	Elisa	1/25/2022	RP-13	13
16	2	2		E42682	DT	0:50	4:20	3:30	Parker	1/25/2022	RP-14	14
17				W2330	KH	0:50	4:20	3:30	Martin	1/25/2022	RP-15	15
18	6	7		E42683	KH	0:50	4:20	3:30	Peter	4/2/2022	RP-16	16
19			1	W2331	KH	0:50	4:20	3:30	Peter	2/1/2022	RP-17	17
20	6	2		E42684	KE	0:50	4:20	3:30	Parker	2/7/2022	RP-18	18
21	4	1	1	W2332	KM	0:50	4:20	3:30	Parker	2/7/2022	RP-19	19
Month

Results sheet,. Notice the 'number of days' column of Dominik and Martin for which the 3rd filter applied to get how many unique days they worked.

Book2
	Q	R	S	T	U	V
24				January	Month
25
26	Total	Allowance	No. of days	Total Jobs	Name	#
27	200.00	100.00	2	3	Dominik	1
28	200.00	100.00	2	2	Elisa	2
29	100.00	100.00	1	2	Martin	3
30	200.00	100.00	2	2	James	4
31	100.00	100.00	1	1	Peter	5
32	100.00	100.00	1	1	Iqbal	6
33	100.00	100.00	1	1	Parker	7
Month

Cell Formulas
Range	Formula
Q27:Q33	Q27	=R27*S27

as i mentioned on my previous post, i would like to create a vba solution for this. cannot risk people messing with formula.

 

[akram-5]

Please help.
any vba suggestion to work around Auto-filtering month, then a loop to check for each date for unique names on that date would help

 

[akram-5]

Problem solved. i changed my order of filtering and copied all data to new sheet for 3rd filter.

Thanks for thinking about helping me.