Extract unique records with date criteria: between 2 dates

[carlmov]

DATE	REGION		LOWER:	1/14/2016
1/11/2016	WEST		UPPER:	1/20/2016
1/12/2016	EAST					unique values
1/13/2016	WEST			REGION
1/14/2016	NORTH			NORTH		WEST
1/12/2016	SOUTH			NORTH		WEST
1/16/2016	NORTH			NORTH		WEST
1/17/2016	NORTH			EAST		WEST
1/13/2016	SOUTH			WEST		WEST
1/19/2016	EAST					WEST
1/20/2016	WEST					WEST
1/16/2016	NORTH					WEST
1/22/2016	SOUTH

<tbody>
</tbody>

Columns: Dates = A, Region = B, etc..

I want to extract only unique records for dates between D1 and D2
The formula below (under Region in red, gives me all records between the dates:

=IFERROR(INDEX($B$2:$B$13,SMALL(IF($A$2:$A$13>=$D$1,IF($A$2:$A$13<=$D$2,ROW($B$2:$B$13)-ROW($B$2)+1)),ROWS($D$5:D5))),"") there was a slight mistake here A2:A13 not A2:A11 sorry

I want to extract only the unique records but it always gives me the same first one although in formula evaluator i get the right row number!!

=IFERROR(INDEX($B$2:$B$13,IF($A$2:$A$13>=$D$1,IF($A$2:$A$13<=$D$2,SMALL(IF(FREQUENCY(MATCH($B$2:$B$13,$B$2:$B$13,0),ROW($B$2:$B$13)-ROW($B$2)+1),ROW($B$2:$B$13)-ROW($B$2)+1),ROWS($E$5:E5))))),"")


Please can anyone help? I don't know what I am doing wrong

Thank you so much

carlo

 

[steve the fish]

Heres one that should work. Table in A1:B13. Dates in D1 and D2. In F2 place this:

=IFERROR(INDEX($B$2:$B$13,MATCH(0,IF($D$1<=$A$2:$A$13,IF($D$2>=$A$2:$A$13,COUNTIF($F$1:F1,$B$2:$B$13))), 0)),"")

Drag down until you get no more results. An array formula so enter CTRL-SHIFT-ENTER.

 

[Marcelo Branco]

Try this array formula in D5 copied down
=IFERROR(INDEX($B:$B,SMALL(IF(ISNA(MATCH(B$2:B$13,D$4:D4,0)),IF(A$2:A$13>=D$1,IF(A$2:A$13<=D$2,ROW(B$2:B$13)))),1)),"")
Ctrl+ShifT+Enter

Hope this helps

M.

 

[TimNevins]

You could also use Advanced Filter and not have to write code or hope you dragged the formula down far enough on your target sheet. Advanced Filter has "unique records" functionality built in.

 

[Aladin Akyurek]

@carlmov

A bit disturbing to see that no one assists with the formula you attempted to use... I'll do so below:

Row\Col	A​	B​	C​	D​	E​
1​	DATE	REGION		LOWER:	1/14/2016
2​	1/11/2016	WEST		UPPER:	1/20/2016
3​	1/12/2016	EAST
4​	1/13/2016	WEST			REGION
5​	1/14/2016	NORTH			WEST
6​	1/12/2016	SOUTH			EAST
7​	1/16/2016	NORTH			NORTH
8​	1/17/2016	NORTH
9​	1/13/2016	SOUTH
10​	1/19/2016	EAST
11​	1/20/2016	WEST
12​	1/16/2016	NORTH

In E5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$13,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$13=""),IF($A$2:$A$13>=$E$1,IF($A$2:$A$13<=$E$2,MATCH($B$2:$B$13,$B$2:$B$13,0)))),ROW($B$2:$B$13)-ROW($B$2)+1),ROW($B$2:$B$13)-ROW($B$2)+1),ROWS($E$5:E5))),"")

 

[carlmov]

Thank you so much Steve, It's simple and works like magic. I wonder what was wrong in my formula (the 2nd one) it looked ok but the result was weird!!

Thank you

 

[carlmov]

Thank you Aladin, I appreciate it a lot. No disrespect, but also Steve the fish gave a formula too.

Thank everyone.
carlo

 

[Aladin Akyurek]

Thank you Aladin, I appreciate it a lot. No disrespect, but also Steve the fish gave a formula too.

Thank everyone.
carlo

The one I endorse (and thought out) is more generic and flexible. (COUNTIF is not fast.)

 

[Marcelo Branco]

Have you seen the formula in post 3? It worked for me.

	A​	B​	C​	D​
1​	DATE​	REGION​	LOWER:​	01/14/2016​
2​	01/11/2016​	WEST​	UPPER:​	01/20/2016​
3​	01/12/2016​	EAST​
4​	01/13/2016​	WEST​		REGION​
5​	01/14/2016​	NORTH​		NORTH​
6​	01/12/2016​	SOUTH​		EAST​
7​	01/16/2016​	NORTH​		WEST​
8​	01/17/2016​	NORTH​
9​	01/13/2016​	SOUTH​
10​	01/19/2016​	EAST​
11​	01/20/2016​	WEST​
12​	01/16/2016​	NORTH​
13​	01/22/2016​	SOUTH​

M.

 

[Marcelo Branco]

A bit disturbing to see that no one assists with the formula you attempted to use...

I've tried to show a different formula taking a different approach. Why not?

M.