Extract unique records with date criteria: between 2 dates

[carlmov]

[TABLE="width: 482"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]REGION[/TD]
[TD][/TD]
[TD]LOWER:[/TD]
[TD]1/14/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/11/2016[/TD]
[TD]WEST[/TD]
[TD][/TD]
[TD]UPPER:[/TD]
[TD]1/20/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/12/2016[/TD]
[TD]EAST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]unique values[/TD]
[/TR]
[TR]
[TD]1/13/2016[/TD]
[TD]WEST[/TD]
[TD][/TD]
[TD][/TD]
[TD]REGION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/14/2016[/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/12/2016[/TD]
[TD]SOUTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/16/2016[/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/17/2016[/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]EAST[/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/13/2016[/TD]
[TD]SOUTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST[/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/19/2016[/TD]
[TD]EAST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/20/2016[/TD]
[TD]WEST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/16/2016[/TD]
[TD]NORTH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]1/22/2016[/TD]
[TD]SOUTH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] DATE[/td][td] REGION[/td][td] [/td][td] LOWER:[/td][td] 1/14/2016[/td][/tr]
[tr][td]

2​

[/td][td] 1/11/2016[/td][td] WEST[/td][td] [/td][td] UPPER:[/td][td] 1/20/2016[/td][/tr]
[tr][td]

3​

[/td][td] 1/12/2016[/td][td] EAST[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

4​

[/td][td] 1/13/2016[/td][td] WEST[/td][td] [/td][td] [/td][td] REGION[/td][/tr]
[tr][td]

5​

[/td][td] 1/14/2016[/td][td] NORTH[/td][td] [/td][td] [/td][td] WEST[/td][/tr]
[tr][td]

6​

[/td][td] 1/12/2016[/td][td] SOUTH[/td][td] [/td][td] [/td][td] EAST[/td][/tr]
[tr][td]

7​

[/td][td] 1/16/2016[/td][td] NORTH[/td][td] [/td][td] [/td][td] NORTH[/td][/tr]
[tr][td]

8​

[/td][td] 1/17/2016[/td][td] NORTH[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

9​

[/td][td] 1/13/2016[/td][td] SOUTH[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

10​

[/td][td] 1/19/2016[/td][td] EAST[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

11​

[/td][td] 1/20/2016[/td][td] WEST[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

12​

[/td][td] 1/16/2016[/td][td] NORTH[/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

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.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

DATE​

[/td][td]

REGION​

[/td][td]

LOWER:​

[/td][td]

01/14/2016​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

01/11/2016​

[/td][td]

WEST​

[/td][td]

UPPER:​

[/td][td]

01/20/2016​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

01/12/2016​

[/td][td]

EAST​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

01/13/2016​

[/td][td]

WEST​

[/td][td][/td][td]

REGION​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

01/14/2016​

[/td][td]

NORTH​

[/td][td][/td][td="bgcolor:#D9D9D9"]

NORTH​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

01/12/2016​

[/td][td]

SOUTH​

[/td][td][/td][td="bgcolor:#D9D9D9"]

EAST​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

01/16/2016​

[/td][td]

NORTH​

[/td][td][/td][td="bgcolor:#D9D9D9"]

WEST​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

01/17/2016​

[/td][td]

NORTH​

[/td][td][/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

01/13/2016​

[/td][td]

SOUTH​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

01/19/2016​

[/td][td]

EAST​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

01/20/2016​

[/td][td]

WEST​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

01/16/2016​

[/td][td]

NORTH​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

01/22/2016​

[/td][td]

SOUTH​

[/td][td][/td][td][/td][/tr]
[/table]

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.