Finding first and last occurances whats the best way?

**Mary87** · 03-02-2012, 10:01 AM

Hi everyone, I'm new here so please bear with me if I've not given you all the details you need!!

I have a spreadsheet of data which I need to manipulate. I need to find the first and last entries for a date but I can't work out how best to do it I've provided an example below, have you any suggestions?
Column A: Date Format YYYY-MM-DD HH:MM
Row 1: 03/01/2012 09:13
Row 2: 03/01/2012 09:13
Row 3: 03/01/2012 09:13
Row 4: 03/01/2012 16:47
Row 5: 03/01/2012 16:59
Row 6: 12/01/2012 09:14
Row 7: 12/01/2012 09:14
Row 8: 12/01/2012 09:14
Row 9: 12/01/2012 17:04
Row 10: 12/01/2012 17:04
Row 11: 12/01/2012 17:42

etc etc...

**Pepe Le Mokko** · 03-02-2012, 10:03 AM

Hi and welcome to the board

Maybe first contact a mod to move your post to a correct forum, and also adapt thread title per forum rules ;-)

**Mary87** · 03-02-2012, 10:17 AM

Hi Pepe Le Mokko,
Thanks for that, I'd not got as far as reading the rules yet, I hope the change to title is better and conforms? :S Where best do you think this question would be suited?
Thanks xx

**NBVC** · 03-02-2012, 10:25 AM

Thread moved to Excel General.

**darknation144** · 03-02-2012, 10:30 AM

So for your example if you were searching 03/01/2012 09:13
It should return row 1 and row 3?

**darknation144** · 03-02-2012, 10:38 AM

Try =MATCH(B2,$A$1:$A$11,0) for the first occurence and =MATCH(B2,$A$1:$A$11,1) for the last occurence.

The first occurence works perfectly the Last occurence seems to be very temperamental.

Anyone help me out why the second formula some times and sometimes doesn't work?

**Mary87** · 03-02-2012, 10:39 AM

Hi darknation144,
It would return row 1,2 and 3 yes. but what I'm actually trying to get (sorry I've not explained myself properly!!) is Row 1 and Row 5, then Row 6 and Row 11. Because they hold the first and the last events for each day. Does that help at all?
Essentially I have a worksheet with thousands of entries but I only need the first and last one for each individual date, so I'm looking to be able to write a macro or use some sort of conditional formatting to do it for me rather than having to go through even line myself to find the data.
Thanks in advance everyone! xx