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

 

[carlmov]

If the ID criteria is in Cell A16 where is the date column? As Mr. Branco replied to your formula - that works too. So you have to determine the date criteria and ID criteria corresponds to which columns. Good luck

 

[egarim]

If the ID criteria is in Cell A16 where is the date column? As Mr. Branco replied to your formula - that works too. So you have to determine the date criteria and ID criteria corresponds to which columns. Good luck

Nice one, Marcelo! Thanks for your help as it works perfectly fine!

carlmov, sorry for the confusion caused. The ID input field is in Cell A16 of my current worksheet whereas the Date column is in Column A of another worksheet where I need my data to be extracted from

 

[Marcelo Branco]

Nice one, Marcelo! Thanks for your help as it works perfectly fine!

You are welcome. Glad to help.

M.

 

[admat]

Maybe this...


[TABLE="class: grid"]
<tbody>[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]
</tbody>[/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.

Hello, good day, can you modify this formula for use with table references instead of ranges?

 

[Marcelo Branco]

Hello, good day, can you modify this formula for use with table references instead of ranges?

Assuming the table name is Table1 (adjust to suit) all you have to do is to replace:
every instance of $A$2:$A$13 by Table1[DATE]
and
every instance of $B$2:$B$13 by Table1[REGION]

M.

 

[admat]

Assuming the table name is Table1 (adjust to suit) all you have to do is to replace:
every instance of $A$2:$A$13 by Table1[DATE]
and
every instance of $B$2:$B$13 by Table1[REGION]

M.

Works great but I have to still reference D$4:D4. If I am putting the results in another table how do I enter this part because Table[#Headers] is not working.

 

[Marcelo Branco]

Sometimes table references complicate things. On the other hand, I do not see any problem in using D$4:D4

M.

 

[admat]

Ok thank you. I am using your formula for payroll. Payroll is done fortnightly but I have a time-sheet that records the times daily. How do I extract the employees worked for each fortnight. For example using the table above, North occurs once between 1-14th and 3 times between 15-31st. I want to get North shown under Region twice (for each fortnight period) or at least for each week ending. North did not occur in the first and last weeks so the results should return North for 2 weeks in January.

 

[admat]

Never mind. I just restructured my table to split Gross pay by each week.