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

 

[Aladin Akyurek]

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

M.

I wasn't addressing that one. [ By the way, I have what I would call a cognitive kind of objection: B:B vs ROW(definite range). ]

 

[Marcelo Branco]

I wasn't addressing that one. [ By the way, I have what I would call a cognitive kind of objection: B:B vs ROW(definite range). ]

It's ok. No problem.

About INDEX(B:B ...)
In certain situations i like to use this type of construction, which does not prejudice the performance in any way, and simplifies the vector
ROW(B$2:B$13)-ROW(B$2)+1

M.

 

[Aladin Akyurek]

It's ok. No problem.

About INDEX(B:B ...)
In certain situations i like to use this type of construction, which does not prejudice the performance in any way, and simplifies the vector
ROW(B$2:B$13)-ROW(B$2)+1

M.

That's exactly what my objection is.

ROW(B$2:B$13)-ROW(B$2)+1

is an integer vector going from 1 to N where N = the size of the range. ROW($B$2:$B$13) is not. More obviously, when you compare ROW($M$90:$M$200) with ROW($M$90:$M$200)-ROW($M$90)+1. The latter is very much the same as INTVECTOR of Longre's morefunc.

 

[carlmov]

Yes I am aware of that, but my template design is such as it requires a formula.
thank you

 

[carlmov]

Sorting the above in ALPHABETICAL ORDER:

I am trying to sort the above result as : EAST, NORTH, WEST.
Now i used : =$B$2:INDEX($B$2:$B$13,MATCH(REPT("z",255),$B$2:$B$13)) Name: sortlist1

and this formula: =IFERROR(INDEX(sortlist1,MATCH(0,COUNTIF(sortlist1,"<"&sortlist1)-SUM(COUNTIF(sortlist1,$B$1:B1)),0)),"")

WHICH WILL EXTRACT ALL THE UNIQUE VALUES regarding between date criteria (thanks to Mike Girvin)

Now to have them sorted alphabetically with the between date criteria i used to following formula:

=IFERROR(INDEX(sortlist1,MATCH(0,IF($D$1<=$B$2:$B$13,IF($D$2>=$B$2:$B$13,COUNTIF(sortlist1,"<"&sortlist1)-SUM(COUNTIF(sortlist1,$D$4:D4)))), 0)),"") (results show in D5 ....)

I get only the first 2 results: East , North, but not West

Please can you help with the correct sorting of the above as per date criteria?? Not sure why it's not giving all 3 answers?

Thank you very much in advance.
carlo

 

[Marcelo Branco]

Maybe this...


[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]

EAST​

[/td][/tr]

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

6​

[/td][td]

01/12/2016​

[/td][td]

SOUTH​

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

NORTH​

[/td][/tr]

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

7​

[/td][td]

01/16/2016​

[/td][td]

NORTH​

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

WEST​

[/td][/tr]

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

8​

[/td][td]

01/17/2016​

[/td][td]

NORTH​

[/td][td][/td][td][/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]

Array formula in D5 copied down
=IFERROR(INDEX($B$2:$B$13,MATCH(0,IF(A$2:A$13>=D$1,IF(A$2:A$13<=D$2,COUNTIFS(B$2:B$13,"<"&B$2:B$13,A$2:A$13,">="&D$1,A$2:A$13,"<="&D$2)-SUM(COUNTIF(D$4:D4,IF(A$2:A$13>=D$1,IF(A$2:A$13<=D$2,B$2:B$13)))))),0)),"")
Ctrl+Shift+Enter

M.

 

[carlmov]

Thank you so much Mr. Marcelo. It works just fine.

Great community here thanks to all.

c

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[egarim]

Hi, I have a similar issue but I do not need unique records.

Column A contains the dates of the transactions. Basically, I want my formula to pull out all the data corresponding to a ID number (input in cell A16) but for transaction date > 1-Jan-17. How do I insert this date range into the formula to make it work. Tried a couple of times but it didn’t work.

=IFERROR(INDEX('OUTGOING SWIFT NEW'!$A$1814:$A$99999,SMALL(IF(ISNUMBER(SEARCH($A$16,'OUTGOING SWIFT NEW'!$I$1814:$I$99999)),ROW('OUTGOING SWIFT NEW'!$I$1814:$I$99999)-ROW('OUTGOING SWIFT NEW'!$I$1814)+1),ROWS(B$16:B16))),"NA")

Thanks in advance!

 

[Marcelo Branco]

Column A contains the dates of the transactions. Basically, I want my formula to pull out all the data corresponding to a ID number (input in cell A16) but for transaction date > 1-Jan-17. How do I insert this date range into the formula to make it work.

Welcome to Mr Excel forum

Maybe this (observe the new condition in blue)
=IFERROR(INDEX('OUTGOING SWIFT NEW'!$A$1814:$A$99999,SMALL(IF(ISNUMBER(SEARCH($A$16,'OUTGOING SWIFT NEW'!$I$1814:$I$99999)),IF('OUTGOING SWIFT NEW'!$A$1814:$A$99999>=DATE(2017,1,1),ROW('OUTGOING SWIFT NEW'!$I$1814:$I$99999)-ROW('OUTGOING SWIFT NEW'!$I$1814)+1)),ROWS(B$16:B16))),"NA")
Ctrl+Shift+Enter

Hope this helps

M.
ps: next time, to improve your chance for getting help, try to create a new thread instead of posting a question in an already solved thread