Sorting, grabbing, and averaging a lot of data into a few cells by date...

[my8950]

I need some assistance trying to grab some data from one column and move it to another.
Let’s say I have column P with dates of the month, the data in this column will change at times.
Say 10 samples per day.

Column “Q” has a number for each sample.

I would like to scan “P” and report the date once in Column “R” and then average each days sample and place the average in “S”.

I realize it sounds crazy, but this is what I’m trying to do. Any ideas or suggestions are appreciated.

"P" is date
"Q" is number
"R" is one of each date in Column "P"
"S" is average of the data of each day in "R"

An example is below:

“P” “Q” “R” “S”

5/29/19	124	5/29/2019	124
5/30/2019	124	5/30/2019	126
5/30/2019	124	5/31/2019	126
5/30/2019	125	6/1/2019	137
5/30/2019	126
5/30/2019	126
5/30/2019	126
5/30/2019	127
5/30/2019	127
5/30/2019	128
5/30/2019	128
5/31/2019	129
5/31/2019	129
5/31/2019	128
5/31/2019	127
5/31/2019	126
5/31/2019	125
5/31/2019	124
5/31/2019	124
5/31/2019	126
5/31/2019	130
6/1/2019	134
6/1/2019	137
6/1/2019	140

<tbody>
</tbody>

 

[DRSteele]

The new functions in Excel 365 make quick work of this type of task. A PivotTable also does it in a jiffy.

Here is the formulaic solution with Excel 365 Insider functions.

Book1
	P	Q	R	S
1	Dates	Value	Date	Avg Value
2	5/29/2019	124	5/29/2019	124.0
3	5/30/2019	124	5/30/2019	126.1
4	5/30/2019	124	5/31/2019	126.8
5	5/30/2019	125	6/1/2019	137.0
6	5/30/2019	126
7	5/30/2019	126
8	5/30/2019	126
9	5/30/2019	127
10	5/30/2019	127
11	5/30/2019	128
12	5/30/2019	128
13	5/31/2019	129
14	5/31/2019	129
15	5/31/2019	128
16	5/31/2019	127
17	5/31/2019	126
18	5/31/2019	125
19	5/31/2019	124
20	5/31/2019	124
21	5/31/2019	126
22	5/31/2019	130
23	6/1/2019	134
24	6/1/2019	137
25	6/1/2019	140
Sheet37

Cell Formulas
Range	Formula
R2	=UNIQUE(P2:P25)
S2	=AVERAGEIFS(Q2:Q25,P2:P25,R2#)

_{^{<strike>
</strike>}}

 

[my8950]

The new functions in Excel 365 make quick work of this type of task. A PivotTable also does it in a jiffy.

Wow, that is perfect, exactly what I want to do...Not sure I can do though with Office 2010.
Thank you!

 

[my8950]

Curious if there is another way to use the "=UNIQUE", we did get Office 2016, but not even close still.
[FONT=&quot]Note:[/FONT][FONT=&quot] September 24, 2018: The UNIQUE function is one of several beta features, and currently only available to a portion of [/FONT]Office Insiders[FONT=&quot] at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and [/FONT]Office 365 subscribers[FONT=&quot].[/FONT]

 

[my8950]

The new functions in Excel 365 make quick work of this type of task. A PivotTable also does it in a jiffy.

Here is the formulaic solution with Excel 365 Insider functions.

<tbody></tbody>

_{^{<strike>
</strike>}}

Oh, thanks for the suggestion of Pivot tables, I've never done before, but did manage to get it to work with that.
Now for some fine tuning!