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: 14

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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.
 
Upvote 0
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!! ;)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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