Findinf the first user time and the last user time

[franciscloete1]

Hi there. Hope someone can help. I have data in A and B that is the Username and times scanned.
I need to get the first time for the username F3 and the last time F6 of the user that they scanned. There is thousanfs of records per user
Please assist me on this

https://drive.google.com/a/francisc...npyR29CN3JDNVBaei1faV9JM3Zn/view?usp=drivesdk

 

[sandy666]

https://drive.google.com/a/francisc...npyR29CN3JDNVBaei1faV9JM3Zn/view?usp=drivesdk

Access denied
Post link without restriction

 

[franciscloete1]

https://africafashionhouse.com/wp-content/uploads/2018/08/need-help.png

 

[Peter_SSs]

Posting a link to a picture doesn't help much as we cannot copy from that picture to test in our workbook. (Most helpers are not interested in manually typing out the sample data. )

Best is to post a small set of dummy sample data and expected results right in your post. My signature block below has help with that.
Failing that you can upload your file to a file-share site and provide a link. However, many of the experienced helpers here choose not to download files from other sites or due to security restrictions at workplaces, are unable to download such files so you will restrict the number of potential helpers if you go down that path

 

[sandy666]

Is taht what you want?

Using PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"First Time", each List.Min([Time]), type time}, {"Last Time", each List.Max([Time]), type time}})
in
    #"Grouped Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]First Time[/td][td=bgcolor:#70AD47]Last Time[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Aaron Sithole[/td][td=bgcolor:#E2EFDA]

06:26:45​

[/td][td=bgcolor:#E2EFDA]

17:26:52​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Afonce Mandlase[/td][td]

06:21:59​

[/td][td]

17:23:13​

[/td][/tr]
[/table]

 

[franciscloete1]

Is there a way to do this without using PowerQuery

 

[Peter_SSs]

Is there a way to do this without using PowerQuery

Yes - quite simple formulas especially if you have Office 365 - and would have posted them long ago if you had posted some sample data that we could actually work with (ref post 4)

So these are untested on your sample data but

- if you have the MINIFS and MAXIFS functions.
F3: =MINIFS(B$2:B$11,A$2:A$11,E3)
F6: =MAXIFS(B$2:B$11,A$2:A$11,E6)
Copy these to the other relevant cells.

- if you don't have those functions, still choices. Either
F3: =AGGREGATE(15,6,B$2:B$11/(A$2:A$11=E3),1)
F6: =AGGREGATE(14,6,B$2:B$11/(A$2:A$11=E6),1)

or these which are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied & pasted.
F3: {=MIN(IF(A$2:A$11=E3,B$2:B$11))}
F6: {=MAX(IF(A$2:A$11=E6,B$2:B$11))}