adjusting formula get data between two dates

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
921
Office Version
  1. 2010
hello i have this formula works based on year, i would amend between two dates the result should be in column e based on column a after writting start date and end date in range e1,g1

1 (1).xlsx
ABCDEFG
1studentdatestart dateend date
2student12019/01/01
3student22019/03/01student
4student32019/05/01 
5student42019/07/01 
6student52019/09/01 
7student12019/11/01 
8student22020/01/01 
9student32020/03/01 
10student42020/05/01 
11student52020/07/01 
12student112020/09/01 
13student122020/11/01 
14student132021/01/01 
15student142021/03/01 
16student152021/05/01 
17student162021/07/01 
18student172021/09/01 
19student182021/11/01 
20student192022/01/01 
21student202022/03/01 
22student212022/05/01 
23student222022/07/01 
24 
Sheet1
Cell Formulas
RangeFormula
E4:E24E4=IFERROR(INDEX($A$2:$A$170, MATCH(0,COUNTIF($E$3:E3, IF(($E$1=YEAR($B$2:$B$170)), $A$2:$A$170, $E$3)), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,181
Office Version
  1. 2016
Platform
  1. Windows
Hi AbdelFatta,

This should do what you want while avoiding an array formula.

AbdetFattah-P2.xlsx
ABCDEFG
1studentdateStart date2/2/2020End date2/1/2021
2student11/1/2019
3student23/1/2019student
4student35/1/2019student3
5student47/1/2019student4
6student59/1/2019student5
7student111/1/2019student11
8student21/1/2020student12
9student33/1/2020student13
10student45/1/2020 
11student57/1/2020 
12student119/1/2020 
13student1211/1/2020 
14student131/1/2021 
15student143/1/2021 
16student155/1/2021 
17student167/1/2021 
18student179/1/2021 
19student1811/1/2021 
20student191/1/2022 
21student203/1/2022 
22student215/1/2022 
23student227/1/2022 
Sheet1
Cell Formulas
RangeFormula
E4:E23E4=IFERROR(INDEX($A$2:$A$23,AGGREGATE(15,6,ROW($A$2:$A$23)-ROW($A$1)/(($B$2:$B$23>=$E$1)*($B$2:$B$23<=$G$1)),ROW()-ROW($E$3))),"")
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
921
Office Version
  1. 2010
hi, toad i would amend the formula if you don't mind i want duplicated data highlighted by yellow color should deleted when search by two dates
1.JPG
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,181
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You can extract into an unused column (maybe hide it?) then de-duplicate into your target area:

AbdetFattah-P2.xlsx
ABCDEFGH
1studentdateStart date2019/1/1End date2020/5/1
2student12019/1/1
3student22019/3/1studentExtract
4student32019/5/1student1student1
5student42019/7/1student2student2
6student52019/9/1student3student3
7student12019/11/1student4student4
8student22020/1/1student5student5
9student32020/3/1 student1
10student42020/5/1 student2
11student52020/7/1 student3
12student112020/9/1 student4
13student122020/11/1  
14student132021/1/1  
15student142021/3/1  
16student152021/5/1  
17student162021/7/1  
18student172021/9/1  
19student182021/11/1  
20student192022/1/1  
21student202022/3/1  
22student212022/5/1  
23student222022/7/1  
v2 (2)
Cell Formulas
RangeFormula
E4:E23E4=IFERROR(INDEX($H$4:$H$23,MATCH(0,INDEX(COUNTIF($E$3:$E3,$H$4:$H$23),),0))&"","")
H4:H23H4=IFERROR(INDEX($A$2:$A$23,AGGREGATE(15,6,ROW($A$2:$A$23)-ROW($A$1)/(($B$2:$B$23>=$E$1)*($B$2:$B$23<=$G$1)),ROW()-ROW($H$3))),"")
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
921
Office Version
  1. 2010
hi, toad it works but i prefer do that without in depend on specific column like h as in this case if is way directly
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff New.xlsm
ABCDEFG
1studentdatestart date01/01/2019end date31/12/2020
2student101/01/2019
3student203/01/2019student
4student305/01/2019student1
5student407/01/2019student2
6student509/01/2019student3
7student111/01/2019student4
8student201/01/2020student5
9student303/01/2020student11
10student405/01/2020student12
11student507/01/2020 
12student1109/01/2020 
13student1211/01/2020 
14student1301/01/2021 
15student1403/01/2021 
16student1505/01/2021 
17student1607/01/2021 
18student1709/01/2021 
19student1811/01/2021 
20student1901/01/2022 
21student2003/01/2022 
22student2105/01/2022 
23student2207/01/2022 
24 
25
Main
Cell Formulas
RangeFormula
E4:E24E4=IFERROR(INDEX($A$2:$A$170, MATCH(0,COUNTIF($E$3:E3, $A$2:$A$170)+($B$2:$B$170<$E$1)+($B$2:$B$170>$G$1), 0)), "")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Can you please post your data, with the formula.
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
921
Office Version
  1. 2010
إ (1).xlsx
ABCDEFG
1studentdatestart date01/01/2019end date05/01/2020
2student101/01/2019
3student203/01/2019student
4student305/01/2019 
5student407/01/2019 
6student509/01/2019 
7student111/01/2019 
8student201/01/2020 
9student303/01/2020 
10student405/01/2020 
11student507/01/2020 
12student1109/01/2020 
13student1211/01/2020 
14student1301/01/2021 
15student1403/01/2021 
16student1505/01/2021 
17student1607/01/2021 
18student1709/01/2021 
19student1811/01/2021 
20student1901/01/2022 
21student2003/01/2022 
22student2105/01/2022 
23student2207/01/2022 
24 
25
26
Sheet1
Cell Formulas
RangeFormula
E4:E24E4=IFERROR(INDEX($A$2:$A$170,MATCH(0,COUNTIF($E$3:E3, $A$2:$A$170)+($B$2:$B$170<$E$1)+($B$2:$B$170>$G$1), 0)), "")
 

Watch MrExcel Video

Forum statistics

Threads
1,119,257
Messages
5,577,019
Members
412,762
Latest member
Philfy
Top