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

 

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

	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​		EAST​
6​	01/12/2016​	SOUTH​		NORTH​
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​

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