Handling a large dataset: turning many datapoints into one

[veenendaalisabelle]

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!

 

[jamescooper]

Welcome to the forum!

Does it matter which value is taken for each day?

 

[veenendaalisabelle]

Welcome to the forum!

Does it matter which value is taken for each day?

Thank you! In principle, it would be alright to take each day the same time, or the first measurement of the day, etc.

 

[Peter_SSs]

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
	A	B	C	D
1
2	15/01/2020 8:00	8	15-Jan-20	5
3	15/01/2020 11:45	5	17-Jan-20	4
4	17/01/2020 7:00	6
5	17/01/2020 8:01	4
6
Daily value

Cell Formulas
Range	Formula
C2:C3	C2	=UNIQUE(INT(A2:A5))
D2:D3	D2	=VLOOKUP(C2+0.999,A$2:B$5,2)
Dynamic array formulas.

 

[Peter_SSs]

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
	A	B	C	D
1
2	15/01/2020 8:00	8	15-Jan-20	8
3	15/01/2020 11:45	5	17-Jan-20	6
4	17/01/2020 7:00	6
5	17/01/2020 8:01	4
6
Daily value

Cell Formulas
Range	Formula
C2:C3	C2	=UNIQUE(INT(A2:A5))
D2:D3	D2	=INDEX(FILTER(B$2:B$5,INT(A$2:A$5)=C2),1)
Dynamic array formulas.

 

[veenendaalisabelle]

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
	A	B	C	D
1
2	15/01/2020 8:00	8	15-Jan-20	5
3	15/01/2020 11:45	5	17-Jan-20	4
4	17/01/2020 7:00	6
5	17/01/2020 8:01	4
6
Daily value

Cell Formulas
Range	Formula
C2:C3	C2	=UNIQUE(INT(A2:A5))
D2:D3	D2	=VLOOKUP(C2+0.999,A$2:B$5,2)
Dynamic array formulas.

Thank you so much, it worked perfectly!!

 

[Peter_SSs]

Thank you so much, it worked perfectly!!

You're welcome. Thanks for the confirmation.

 

[veenendaalisabelle]

You're welcome. Thanks for the confirmation.

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?

 

[Peter_SSs]

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
	A	B	C	D
1
2	15/01/2020 8:00	8	15-Jan-20	6.5
3	15/01/2020 11:45	5	17-Jan-20	5
4	17/01/2020 7:00	6
5	17/01/2020 8:01	4
6
Daily value

Cell Formulas
Range	Formula
C2:C3	C2	=UNIQUE(INT(A2:A5))
D2:D3	D2	=AVERAGEIFS(B$2:B$5,A$2:A$5,">="&C2,A$2:A$5,"<"&C2+1)
Dynamic array formulas.