Finding First and Last Entries for Each Day Across Multiple Units

W

whitehorse091

New Member
Joined
Aug 3, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi! I have a set of data with the dates, time and data recorded for multiple units. How do I get the first and last entries of each day for each unit? I've only managed to do so by filtering the unit and get the first and last entries for each day but it would be more efficient if I could find a way to do so for all the units using the same spreadsheet. I have included some sample data below. Would appreciate any help I can get!🥲

UnitaDateTimeRecorded Data
A
11/16/2022 16:24​
1527.19199​
A
11/16/2022 15:54​
1527.15242​
A
11/16/2022 15:31​
1527.01937​
A
11/16/2022 14:41​
1526.95051​
A
11/16/2022 14:21​
1526.89561​
A
11/16/2022 13:32​
1526.81712​
A
11/16/2022 13:18​
1526.63515​
A
11/16/2022 12:37​
1524.02116​
A
11/16/2022 10:51​
1515.89618​
A
11/16/2022 9:04​
1507.79041​
A
11/16/2022 8:47​
1506.50191​
A
11/16/2022 8:29​
1505.84128​
B
11/16/2022 12:56​
1526.17615​
B
11/16/2022 12:27​
1524.07688​
B
11/16/2022 10:46​
1518.04296​
B
11/16/2022 8:31​
1509.77978​
C
11/15/2022 14:23​
1530.14487​
C
11/15/2022 13:31​
1526.96938​
C
11/15/2022 11:33​
1521.87769​
C
11/15/2022 11:17​
1521.43367​
C
11/15/2022 8:24​
1514.53326​
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Is it are you looking for?

Baccarat Ratings XX.xlsm
ABCDEFGHI
1UnitDateValueUnitDateValue
2A16-Nov 19:001527.19199A16-Nov 08:001505.84128
3A16-Nov 18:001527.15242A16-Nov 19:001527.19199
4A16-Nov 17:001527.01937B16-Nov 04:001509.77978
5A16-Nov 16:001526.95051B16-Nov 07:001526.17615
6A16-Nov 15:001526.89561C15-Nov 19:001514.53326
7A16-Nov 14:001526.81712C15-Nov 23:001530.14487
8A16-Nov 13:001526.63515   
9A16-Nov 12:001524.02116   
10A16-Nov 11:001515.89618   
11A16-Nov 10:001507.79041   
12A16-Nov 09:001506.50191   
13A16-Nov 08:001505.84128   
14B16-Nov 07:001526.17615
15B16-Nov 06:001524.07688
16B16-Nov 05:001518.04296
17B16-Nov 04:001509.77978
18C15-Nov 23:001530.14487
19C15-Nov 22:001526.96938
20C15-Nov 21:001521.87769
21C15-Nov 20:001521.43367
22C15-Nov 19:001514.53326
Sheet1
Cell Formulas
RangeFormula
G2G2=A2
H2:H13H2=IF(G2="","",LOOKUP(2,1/($B$2:$B$22=AGGREGATE(IF(COUNTIF($G$2:$G2,$G2)=1,15,14),6,$B$2:$B$22/($A$2:$A$22=$G2),1)),B$2:B$22))
I2:I13I2=IF(G2="","",LOOKUP(2,1/($B$2:$B$22=AGGREGATE(IF(COUNTIF($G$2:$G2,$G2)=1,15,14),6,$B$2:$B$22/($A$2:$A$22=$G2),1)),C$2:C$22))
G3:G13G3=IFERROR(IF(COUNTIF($G$2:G2,G2)=1,G2,INDEX($A$2:$A$22,MATCH(0,INDEX(COUNTIF($G$1:G2,$A$2:$A$22),),0))),"")
 
Upvote 0
bebo021999 said:
Is it are you looking for?

Baccarat Ratings XX.xlsm
ABCDEFGHI
1UnitDateValueUnitDateValue
2A16-Nov 19:001527.19199A16-Nov 08:001505.84128
3A16-Nov 18:001527.15242A16-Nov 19:001527.19199
4A16-Nov 17:001527.01937B16-Nov 04:001509.77978
5A16-Nov 16:001526.95051B16-Nov 07:001526.17615
6A16-Nov 15:001526.89561C15-Nov 19:001514.53326
7A16-Nov 14:001526.81712C15-Nov 23:001530.14487
8A16-Nov 13:001526.63515   
9A16-Nov 12:001524.02116   
10A16-Nov 11:001515.89618   
11A16-Nov 10:001507.79041   
12A16-Nov 09:001506.50191   
13A16-Nov 08:001505.84128   
14B16-Nov 07:001526.17615
15B16-Nov 06:001524.07688
16B16-Nov 05:001518.04296
17B16-Nov 04:001509.77978
18C15-Nov 23:001530.14487
19C15-Nov 22:001526.96938
20C15-Nov 21:001521.87769
21C15-Nov 20:001521.43367
22C15-Nov 19:001514.53326
Sheet1
Cell Formulas
RangeFormula
G2G2=A2
H2:H13H2=IF(G2="","",LOOKUP(2,1/($B$2:$B$22=AGGREGATE(IF(COUNTIF($G$2:$G2,$G2)=1,15,14),6,$B$2:$B$22/($A$2:$A$22=$G2),1)),B$2:B$22))
I2:I13I2=IF(G2="","",LOOKUP(2,1/($B$2:$B$22=AGGREGATE(IF(COUNTIF($G$2:$G2,$G2)=1,15,14),6,$B$2:$B$22/($A$2:$A$22=$G2),1)),C$2:C$22))
G3:G13G3=IFERROR(IF(COUNTIF($G$2:G2,G2)=1,G2,INDEX($A$2:$A$22,MATCH(0,INDEX(COUNTIF($G$1:G2,$A$2:$A$22),),0))),"")
Click to expand...
bebo021999, I really appreciate your help. I'm not quite there yet, because I need to find the first and last entries of each day for each unit. My apologies for not uploading a clearer sample data.

UnitaDateTimeRecorded Data
A11/16/2022 16:241527.19199
A11/16/2022 15:541527.15242
A11/16/2022 15:311527.01937
A11/16/2022 4:411526.95051
A11/15/2022 14:211526.89561
A11/15/2022 13:321526.81712
A11/15/2022 13:181526.63515
A11/15/2022 12:371524.02116
A11/15/2022 5:511515.89618
A11/14/2022 9:041507.79041
A11/14/2022 8:471506.50191
A11/14/2022 8:291505.84128
B11/16/2022 12:561526.17615
B11/16/2022 2:271524.07688
B11/15/2022 10:461518.04296
B11/15/2022 8:311509.77978
C11/15/2022 14:231530.14487
C11/15/2022 9:311526.96938
C11/14/2022 14:331521.87769
C11/14/2022 11:171521.43367
C11/14/2022 8:241514.53326
 
Upvote 0
It's a bit long winded but i have run out of time for now so thought i would share what i had.

Book1.xlsx
ABCDEFG
1UnitaDateTimeRecorded DataUnitaDateTimeRecorded Data
2A16/11/2022 16:241527.19199A16/11/2022 16:241527.19199
3A16/11/2022 15:541527.15242A16/11/2022 04:411526.95051
4A16/11/2022 15:311527.01937A15/11/2022 14:211526.89561
5A16/11/2022 04:411526.95051A15/11/2022 05:511515.89618
6A15/11/2022 14:211526.89561A14/11/2022 09:041507.79041
7A15/11/2022 13:321526.81712A14/11/2022 08:291505.84128
8A15/11/2022 13:181526.63515B16/11/2022 12:561526.17615
9A15/11/2022 12:371524.02116B16/11/2022 02:271524.07688
10A15/11/2022 05:511515.89618B15/11/2022 10:461518.04296
11A14/11/2022 09:041507.79041B15/11/2022 08:311509.77978
12A14/11/2022 08:471506.50191C15/11/2022 14:231530.14487
13A14/11/2022 08:291505.84128C15/11/2022 09:311526.96938
14B16/11/2022 12:561526.17615C14/11/2022 14:331521.87769
15B16/11/2022 02:271524.07688C14/11/2022 08:241514.53326
16B15/11/2022 10:461518.04296
17B15/11/2022 08:311509.77978
18C15/11/2022 14:231530.14487
19C15/11/2022 09:311526.96938
20C14/11/2022 14:331521.87769
21C14/11/2022 11:171521.43367
22C14/11/2022 08:241514.53326
Sheet1
Cell Formulas
RangeFormula
E2:G15E2=LET(data,A2:C22, aData,SORT(HSTACK(INDEX(data,,1)&TRUNC(INDEX(data,,2)),data),{2,3},{1,-1}), aLkp,UNIQUE(INDEX(data,,1)&TRUNC(INDEX(data,,2))), rMax,HSTACK(VLOOKUP(aLkp,aData,2,0),VLOOKUP(aLkp,aData,3,0),VLOOKUP(aLkp,aData,4,0)), dData,SORT(HSTACK(INDEX(data,,1)&TRUNC(INDEX(data,,2)),data),{2,3},{1,1}), rMin,HSTACK(VLOOKUP(aLkp,dData,2,0),VLOOKUP(aLkp,dData,3,0),VLOOKUP(aLkp,dData,4,0)), SORT(VSTACK(rMax,rMin),{1,2},{1,-1}))
Dynamic array formulas.
 
Upvote 0
@whitehorse091
When providing sample data, it is always a good idea to also provide the expected result(s), entered manually, so helpers can see jsut what you are aiming for.

Would this be any use?

23 08 06.xlsm
ABCDEFGHIJK
1UnitaDateTimeRecorded DataMINMAX
2A16/11/2022 16:241527.19199Unit/Date14/11/202215/11/202216/11/202214/11/202215/11/202216/11/2022
3A16/11/2022 15:541527.15242A1505.841281515.896181526.950511507.790411526.895611527.19199
4A16/11/2022 15:311527.01937B 1509.779781524.07688 1518.042961526.17615
5A16/11/2022 4:411526.95051C1514.533261526.96938 1521.877691530.14487 
6A15/11/2022 14:211526.89561
7A15/11/2022 13:321526.81712
8A15/11/2022 13:181526.63515
9A15/11/2022 12:371524.02116
10A15/11/2022 5:511515.89618
11A14/11/2022 9:041507.79041
12A14/11/2022 8:471506.50191
13A14/11/2022 8:291505.84128
14B16/11/2022 12:561526.17615
15B16/11/2022 2:271524.07688
16B15/11/2022 10:461518.04296
17B15/11/2022 8:311509.77978
18C15/11/2022 14:231530.14487
19C15/11/2022 9:311526.96938
20C14/11/2022 14:331521.87769
21C14/11/2022 11:171521.43367
22C14/11/2022 8:241514.53326
First Last per Day
Cell Formulas
RangeFormula
F2:K2F2=LET(d,TRANSPOSE(SORT(UNIQUE(INT(B2:B22)))),HSTACK(d,d))
E3:E5E3=UNIQUE(A2:A22)
F3:H5F3=IFERROR(AGGREGATE(15,6,$C$2:$C$22/((INT($B$2:$B$22)=F$2)*($A$2:$A$22=$E3)),1),"")
I3:K5I3=IFERROR(AGGREGATE(14,6,$C$2:$C$22/((INT($B$2:$B$22)=I$2)*($A$2:$A$22=$E3)),1),"")
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

Share this page

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