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

sparkd

New Member
Joined
Jul 23, 2018
Messages
12
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top