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

my8950

Board Regular
Joined
May 1, 2009
Messages
162
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”
[TABLE="width: 268"]
<tbody>[TR]
[TD]5/29/19
[/TD]
[TD]124
[/TD]
[TD]5/29/2019
[/TD]
[TD]124
[/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]124
[/TD]
[TD]5/30/2019
[/TD]
[TD]126
[/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]124
[/TD]
[TD]5/31/2019
[/TD]
[TD]126
[/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]125
[/TD]
[TD]6/1/2019
[/TD]
[TD]137
[/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]126
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]126
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]126
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]127
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]127
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]128
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/30/2019
[/TD]
[TD]128
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]129
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]129
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]128
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]127
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]126
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]125
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]124
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]124
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]126
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]130
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2019
[/TD]
[TD]134
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2019
[/TD]
[TD]137
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2019
[/TD]
[TD]140
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
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
Cell Formulas
RangeFormula
R2=UNIQUE(P2:P25)
S2=AVERAGEIFS(Q2:Q25,P2:P25,R2#)
<strike>
</strike>
 
Last edited:
Upvote 0
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]
 
Upvote 0
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.[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]

<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!
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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