# Formula help to extract and list UNIQUE dates and list them chronilogically

#### sparkd

##### New Member
I currently have a workbook with a list of Dates/Times: B2:B200000The cells in this range are formatted asdd/mm/yyyy hh:mm:ssI want to extract from this list UNIQUE dates and list these from cell H7 downwardsI have tried INDEX and MATCH but don't seem to be able to crack it.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Special-K99

##### Well-known Member
in H7
=IFERROR(TEXT(INDEX(\$B\$2:\$B\$200000,MATCH(0,INDEX(COUNTIF(\$H\$6:H6,TEXT(\$B\$2:\$B\$200000,"dd/mm/yyyy")),0,0),0)),"dd/mm/yyyy"),"")
Array formula, use Ctrl-Shift-Enter

May be slow as you have a large range

#### sparkd

##### New Member
in H7
=IFERROR(TEXT(INDEX(\$B\$2:\$B\$200000,MATCH(0,INDEX(COUNTIF(\$H\$6:H6,TEXT(\$B\$2:\$B\$200000,"dd/mm/yyyy")),0,0),0)),"dd/mm/yyyy"),"")
Array formula, use Ctrl-Shift-Enter

May be slow as you have a large range

Thanks this works great.

How would I amend the formula so the range is DYNAMIC..... eg replaces the existing \$B\$2:\$B\$200000 with

something like ="\$B\$1:\$B\$"&COUNTA(A:A)

#### Special-K99

##### Well-known Member
Try

INDIRECT("\$B\$2:\$B"&COUNTA(A:A)) in the formula

though INDIRECT is not efficient.

Also my formula doesnt list them chronologically. not sure how you'd do that

Last edited:

Replies
8
Views
77
Replies
3
Views
55
Replies
5
Views
85
Replies
30
Views
1K
Replies
7
Views
46

1,109,333
Messages
5,528,089
Members
409,801
Latest member
Jamaira

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...