List of names based on City in accending order between to set date limits

[Muthuraman224]

Book1
	H	I	J
3	Date	Name	City
4	01/01/2024	Liam	Newyork
5	06/01/2024	Noah	London
6	02/01/2024	Oliver	Newyork
7	10/01/2024	James	switzerland
8	03/01/2024	Elijah	Newyork
9	03/01/2024	William	Newyork
10	05/01/2024	Henry	Newyork
11	02/01/2024	Lucas	London
12	07/01/2024	Benjamin	switzerland
13	04/01/2024	Theodore	Paris
14	06/01/2024	Louis	Paris
15	07/01/2024	Zaltan	Paris
16	08/01/2024	Piliph	Paris
Sheet1

 

[Peter_SSs]

Welcome to the MrExcel board!

For that sample data, would you care to give us an example of the date limits and also the expected results for those date limits?

 

[Muthuraman224]

Welcome to the MrExcel board!

For that sample data, would you care to give us an example of the date limits and also the expected results for those date limits?

If the following were the criteria
Start date - 01.01.2024
End Date - 06.01.2023
City - Newyork.

with these criteria, I would like to get the results in Ascending order based on the Date within the 2 set limits.

 

[Peter_SSs]

If the following were the criteria
Start date - 01.01.2024
End Date - 06.01.2023

How can the Start date be after the End date?

Also, are your dates m/d/y or d/m/y format?

 

[Muthuraman224]

How can the Start date be after the End date?

Also, are your dates m/d/y or d/m/y format?

If the following were the criteria
Start date - 01.01.2024
End Date - 06.01.2024
City - Newyork.

with these criteria, I would like to get the results in Ascending order based on the Date within the 2 set limits.

 

[Peter_SSs]

What about this question?

Also, are your dates m/d/y or d/m/y format?

 

[Muthuraman224]

How can the Start date be after the End date?

Also, are your dates m/d/y or d/m/y format?

dates are in DD/MM/YYYY format

 

[Peter_SSs]

Thanks. Try this then.

24 01 08.xlsm
	H	I	J	K	L	M	N
1
2
3	Date	Name	City		Start	1/01/2024
4	1/01/2024	Liam	Newyork		End	6/01/2024
5	6/01/2024	Noah	London		City	Newyork
6	2/01/2024	Oliver	Newyork		List	1/01/2024	Liam
7	10/01/2024	James	switzerland			2/01/2024	Oliver
8	3/01/2024	Elijah	Newyork			3/01/2024	Elijah
9	3/01/2024	William	Newyork			3/01/2024	William
10	5/01/2024	Henry	Newyork			5/01/2024	Henry
11	2/01/2024	Lucas	London
12	7/01/2024	Benjamin	switzerland
13	4/01/2024	Theodore	Paris
14	6/01/2024	Louis	Paris
15	7/01/2024	Zaltan	Paris
16	8/01/2024	Piliph	Paris
List

Cell Formulas
Range	Formula
M6:M16	M6	=IFERROR(AGGREGATE(15,6,H$4:H$16/((H$4:H$16>=M$3)*(H$4:H$16<=M$4)*(J$4:J$16=M$5)),ROWS(M$6:M6)),"")
N6:N16	N6	=IF(M6="","",INDEX(I:I,AGGREGATE(15,6,ROW(I$4:I$16)/((H$4:H$16=M6)*(J$4:J$16=M$5)),COUNTIF(M$6:M6,M6))))

 

[Muthuraman224]

Thanks. Try this then.

24 01 08.xlsm
	H	I	J	K	L	M	N
1
2
3	Date	Name	City		Start	1/01/2024
4	1/01/2024	Liam	Newyork		End	6/01/2024
5	6/01/2024	Noah	London		City	Newyork
6	2/01/2024	Oliver	Newyork		List	1/01/2024	Liam
7	10/01/2024	James	switzerland			2/01/2024	Oliver
8	3/01/2024	Elijah	Newyork			3/01/2024	Elijah
9	3/01/2024	William	Newyork			3/01/2024	William
10	5/01/2024	Henry	Newyork			5/01/2024	Henry
11	2/01/2024	Lucas	London
12	7/01/2024	Benjamin	switzerland
13	4/01/2024	Theodore	Paris
14	6/01/2024	Louis	Paris
15	7/01/2024	Zaltan	Paris
16	8/01/2024	Piliph	Paris
List

Cell Formulas
Range	Formula
M6:M16	M6	=IFERROR(AGGREGATE(15,6,H$4:H$16/((H$4:H$16>=M$3)*(H$4:H$16<=M$4)*(J$4:J$16=M$5)),ROWS(M$6:M6)),"")
N6:N16	N6	=IF(M6="","",INDEX(I:I,AGGREGATE(15,6,ROW(I$4:I$16)/((H$4:H$16=M6)*(J$4:J$16=M$5)),COUNTIF(M$6:M6,M6))))

Thanks a lot.!! with huge respect. , I was cracking my head with the combination of index, offset, counta, match & small formulas. but you came up with a smart solution in a very short time.. much appreciate your dedication and knowledge sharing.

 

[Peter_SSs]

You are welcome. Glad to help. Thanks for the follow-up.