Duplicates in Multiple criteria

[sumsaam]

i can get punch time of every employee through multiple criteria formula. my question is that every employee has 2,3 or 4 punches in one day. how to get all values saparately. i mean to say that if result of multiple criteria returns two or more values, then how to get all these values saparately

13-03-2023 - Copy.xlsx
	A	B	C	D
1	Emp ID	First Name	Date	Time
2	3	Mustafa	13-03-2023	12:12
3	4	Afaq	13-03-2023	11:16
4	9	Ali	13-03-2023	11:16
5	10	Saif	13-03-2023	11:16
6	10	Saif	13-03-2023	16:54
7	2	Riaz	13-03-2023	11:16
8	11	Afnan	13-03-2023	11:16
Sheet

 

[Joe4]

I suggest that you 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’). There are some new functions in the newer versions that can make these tasks much simplerm but we do not want to suggest them if you do not have that version.

 

[sumsaam]

Tell me if there is any formula for this

 

[Joe4]

This can be done quite easily in Excel versions 2019 and newer, as the introduced a FILTER function that makes this simple.
But it is not available in Excel 2010. The combinations of functions to do it can be a bit complex, and are shown here: 5 easy ways to VLOOKUP and return multiple values

It can be done more simply using the FILTER method (not the function) that exists in 2010, but that is not a formula.
VBA is another option, if you do not like the long formulaic methods shown in the link I provided or by using the FILTER method.

 

[awoohaw]

Here is a non 365 solution. It is tricky, but makes sense:
I'm not sure if Aggregate functions are in 2010, but I think they are. Also, i'm unsure if you have to enter using the CTRL-SHFT-ENTER keystroke or not.

mr excel questions 29.xlsm
	A	B	C	D	E	F	G	H	I
1	Emp ID	First Name	Date	Time
2	3	Mustafa	13-03-2023	12:12		Mustafa	12:12
3	4	Afaq	13-03-2023	11:16		Afaq	11:16
4	9	Ali	13-03-2023	11:16		Ali	11:16
5	10	Saif	13-03-2023	11:16		Saif	11:16	16:54
6	10	Saif	13-03-2023	16:54		Riaz	11:16
7	2	Riaz	13-03-2023	11:16		Afnan	11:16
8	11	Afnan	13-03-2023	11:16
sumsaam

Cell Formulas
Range	Formula
G2:I8	G2	=IF(COUNTIF($B$2:$B$8,$F2)<COLUMNS($G2:G2),"",AGGREGATE(15,6,($F2=$B$2:$B$8)/($F2=$B$2:$B$8)*($D$2:$D$8),COLUMN(G2)-6))

 

[awoohaw]

@sumsaam , did you find an answer here?