Index Match Date Range with no repeating results

[TheJonWithNoH]

I have a worksheet that pulls a list of data from another sheet and I was able to create the formula so repeating data does not reoccur on my list. My problem now is that I need to edit this formula further to only include data for a certain date range. Here is an example of the data sheet:

Group Name	Date
ABC Group	9/22/2017
ABC Group	9/23/2017
ABC Group	9/24/2017
XYZ Group	9/15/2017
XXX Group	10/1/2017
XXX Group	10/2/2017
QQQ Group	9/30/2017
ZZZ Group	11/1/2017
ZZZ Group	11/2/2017

<tbody>
</tbody>

My current formula is an array formula that removes duplicates and just provides me with a list of Group Names:

{=INDEX(Data!A$1:INDEX(Data!A:A,MATCH("zzz",Data!A:A)),MATCH(0,COUNTIF(Z$1:Z1,Data!A$1:INDEX(Data!A:A,MATCH("zzz",Data!A:A))&""),0))}

This formula provides me with these results:

ABC Group

XYZ Group

XXX Group

QQQ Group

ZZZ Group

<tbody>
</tbody>

How can I modify this formula to only provide me the data for September groups? (ABC, XYZ, QQQ Groups should only be listed)


Thank you in advance for your help!

 

[Aladin Akyurek]

1. Define Lrow in Name Manager as referring to:

=MATCH(9.99999999999999E+307,Data!$B:$B)

2. Define Groups in Name Manager as referring to:

=Data!$A$1:INDEX(Data!$A:$A,Lrow)

3. Define Dates in Name Manager as referring to:

=Data!$B$1:INDEX(Data!$B:$B,Lrow)

4. Define Ivec in Name Manager as referring to:

=ROW(Groups)-ROW(INDEX(Groups,1,1))+1

5. In Z1 enter: 9/1/2017

6. In Z2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Groups=""),IF(Dates-DAY(Dates)+1=$Z$1,MATCH(Groups,Groups,0))),Ivec),1))

7. In Z3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($Z$3:Z3)>$Z$2,"",INDEX(Groups,SMALL(IF(FREQUENCY(IF(1-(Groups=""),IF(Dates-DAY(Dates)+1=$Z$1,MATCH(Groups,Groups,0))),Ivec),Ivec),ROWS($Z$3:Z3))))

 

[TheJonWithNoH]

This worked perfectly, thank you!!

 

[Aladin Akyurek]

This worked perfectly, thank you!!

You are welcome. Thanks for the update.