# Thread: Sorting, grabbing, and averaging a lot of data into a few cells by date... Thanks:  1 Post #5295709 (1) Likes: 0

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

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

2. ## Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

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.

PQRS
1DatesValueDateAvg Value
25/29/20191245/29/2019124.0
35/30/20191245/30/2019126.1
45/30/20191245/31/2019126.8
55/30/20191256/1/2019137.0
65/30/2019126
75/30/2019126
85/30/2019126
95/30/2019127
105/30/2019127
115/30/2019128
125/30/2019128
135/31/2019129
145/31/2019129
155/31/2019128
165/31/2019127
175/31/2019126
185/31/2019125
195/31/2019124
205/31/2019124
215/31/2019126
225/31/2019130
236/1/2019134
246/1/2019137
256/1/2019140

Sheet37

Worksheet Formulas
CellFormula
R2=UNIQUE(P2:P25)
S2=AVERAGEIFS(Q2:Q25,P2:P25,R2#)

3. ## Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

Originally Posted by DRSteele
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!

4. ## Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

Curious if there is another way to use the "=UNIQUE", we did get Office 2016, but not even close still.
Note: September 24, 2018: The UNIQUE function is one of several beta features, and currently only available to a portion of Office Insiders 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 Office 365 subscribers.

5. ## Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

Originally Posted by 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.

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!