Handling a large dataset: turning many datapoints into one

veenendaalisabelle

New Member
Joined
May 14, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all, I have a problem with analyzing a large dataset for my thesis.

My problem is this: I have a huge dataset of a river gauge, with measurements on certain parameters. These parameters are measured for period of 30 years, daily, every half hour. The problem is, I only need 1 value per day for my analysis. So currently, I've got this incredibly long list of measurements every day from 00:00 until 23:30, which I want to turn into a list of per-day values, to do trend and time-series analysis.

This would have been an "easy" problem to solve these measurements were consistent. However, some days are not included, and some hours are missing on certain days. Can someone help me out, and tell me how I can create the list that I am looking for? I am not an extremely experienced excel-user, at least not very professional, so coding etc. will possibly not work for me.

In the figure attached you can see part of one of the datasets I am using.

Thank you!
 

Attachments

  • Capture.PNG
    Capture.PNG
    70.7 KB · Views: 8

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Hence my tiny example below.

IF your version of Excel 365 has the UNIQUE function then just enter the C2 formula shown in that cell only and the other values will automatically 'spill' down the column.
Then enter the column D formula and copy down. This gives the last value each day. Would that be sufficient?
Note that my dates are in d/m/y format.
Dates/Times in column A need to be in ascending order but that seems to be the case for you.

20 05 14.xlsm
ABCD
1
215/01/2020 8:00815-Jan-205
315/01/2020 11:45517-Jan-204
417/01/2020 7:006
517/01/2020 8:014
6
Daily value
Cell Formulas
RangeFormula
C2:C3C2=UNIQUE(INT(A2:A5))
D2:D3D2=VLOOKUP(C2+0.999,A$2:B$5,2)
Dynamic array formulas.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This gives the last value each day. Would that be sufficient?
For the first value of the day, try this column D formula copied down (again IF you have the new dynamic array functions. - FILTER this time)

20 05 14.xlsm
ABCD
1
215/01/2020 8:00815-Jan-208
315/01/2020 11:45517-Jan-206
417/01/2020 7:006
517/01/2020 8:014
6
Daily value
Cell Formulas
RangeFormula
C2:C3C2=UNIQUE(INT(A2:A5))
D2:D3D2=INDEX(FILTER(B$2:B$5,INT(A$2:A$5)=C2),1)
Dynamic array formulas.
 

veenendaalisabelle

New Member
Joined
May 14, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Hence my tiny example below.

IF your version of Excel 365 has the UNIQUE function then just enter the C2 formula shown in that cell only and the other values will automatically 'spill' down the column.
Then enter the column D formula and copy down. This gives the last value each day. Would that be sufficient?
Note that my dates are in d/m/y format.
Dates/Times in column A need to be in ascending order but that seems to be the case for you.

20 05 14.xlsm
ABCD
1
215/01/2020 8:00815-Jan-205
315/01/2020 11:45517-Jan-204
417/01/2020 7:006
517/01/2020 8:014
6
Daily value
Cell Formulas
RangeFormula
C2:C3C2=UNIQUE(INT(A2:A5))
D2:D3D2=VLOOKUP(C2+0.999,A$2:B$5,2)
Dynamic array formulas.

Thank you so much, it worked perfectly!! ;)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
Hi Peter! I was wondering if there is also a way to get the average of those values, instead of only the first or the last?
Sure ..

20 05 14.xlsm
ABCD
1
215/01/2020 8:00815-Jan-206.5
315/01/2020 11:45517-Jan-205
417/01/2020 7:006
517/01/2020 8:014
6
Daily value
Cell Formulas
RangeFormula
C2:C3C2=UNIQUE(INT(A2:A5))
D2:D3D2=AVERAGEIFS(B$2:B$5,A$2:A$5,">="&C2,A$2:A$5,"<"&C2+1)
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,325
Messages
5,547,263
Members
410,783
Latest member
sonnny
Top