Find first occurrence for each day

[davidphi]

Hi,

I have a list of date / time fields where I want to find the earliest occurrence for a particular day. Some example data is:

29/01/2020 09:07
29/01/2020 09:59
29/01/2020 15:09
30/01/2020 09:08
30/01/2020 10:12
30/01/2020 12:58
30/01/2020 14:16
30/01/2020 16:15
31/01/2020 09:01
31/01/2020 10:53

So - from this list I'd like a formula to return true for 29/01/2020 09:07, 30/01/2020 09:08 and 31/01/2020 09:01, but false for everything else.

My ultimate aim, once I've got this, is that I'd ideally also like to introduce another column of data with different items - similar to

29/01/2020 09:07 Duck
29/01/2020 09:59 Sheep
29/01/2020 15:09 Sheep
30/01/2020 09:08 Cow
30/01/2020 10:12 Goat
30/01/2020 12:58 Duck
30/01/2020 14:16 Duck
30/01/2020 16:15 Goat

I'd then like the formula to return true for earliest time each of the animals had a date/time logged on a given day eg:

Duck - 29/01/2020 09:07 and 30/01/2020 12:58
Sheep - 29/01/2020 09:59
Cow - 30/01/2020 09:08
Goat - 30/01/2020 10:12

I've tried this using an index / match formula, but I can't work it out!

Thanks,
David

 

[sandy666]

is that what you want?

 

[sandy666]

or maybe

 

[davidphi]

is that what you want?

View attachment 5356

Wow - awesome. It's the first one above that I'd like with the date, item and time. That looks great!

 

[sandy666]

sure, here is M-code for Power Query
(this is NOT vba !)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"date", type date}, {"time", type time}, {"item", type text}}),
    Group = Table.Group(Type, {"date", "item"}, {{"time", each List.Min([time]), type time}})
in
    Group

btw. Update profile (Account details) about Excel version - less troubles & wasted posts

 

[davidphi]

Awesome - that worked great. First time I've used the power query. That seems great!

 

[sandy666]

Awesome - that worked great. First time I've used the power query. That seems great!

You are welcome
Thanks for the feedback

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help