How to look up the most often occuring word within a date range?

[kellyfirth]

I'm trying to create a 'Daily Stats' sheet on excel. On my 'Original Data' sheet - Column A will have a list of dates and corresponding data alongside this

Eg. :

09/02/2016 Apples
09/02/2016 Bananas
09/02/2016 Grapes
09/02/2016 Apples
10/02/2016 Bananas
10/02/2016 Bananas
11/02/2016 Grapes

On the Daily Stats sheet, I want to be able to write a date in cell A1 (eg. 09/02/2016) and it will tell me what the most frequently occuring word is for this date in column B of the Original data sheet. So if I enter 09/02/2016, the result would be Bananas as this is the most frequently occuring word within that date range. However, I want this to work so that it's a variable range? - So I don't have to say, look at A1:A4, just look in column A for this date. Does that make any sense whatsoever? :(

[MarvinP]

Hi kellyfirth,

I think you are asking for a Dynamic Named Range to deal with the "Variable Range" problem. See:
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.jkp-ads.com/articles/ExcelNames07.asp

See my example on how I'd do this problem. What if you have a new fruit? It is hard to deal with that without a Pivot. What if the names of the fruit or dates change. See the attached and give it a try.

[José Augusto]

Or try these

Please Login or Register  to view this content.

where B3:C9 is yor data and G3 the date

see the file Sample (6).xlsx

[Tony Valko]

Maybe this...

Data Range

	A	B	C	D	E
1	9/2/2016	Apples	------	9/2/2016	Apples
2	9/2/2016	Bananas
3	9/2/2016	Grapes
4	9/2/2016	Apples
5	10/2/2016	Bananas
6	10/2/2016	Bananas
7	11/2/2016	Grapes
8
9
10

This array formula** entered in E1:

=INDEX(B1:B10,MODE(IF(A1:A10<>"",IF(A1:A10=D1,MATCH(B1:B10,B1:B10,0)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes that the most occurring entry will appear more than once. If not the formula will return an error.

[José Augusto]

Hi kellyfirth,
In post #3 I forgot to say that it was a formula array.
Tony Valko presents a better formula.

[kellyfirth]

Great, thank you! I've used Tony Valko's formula as this seems easiest to follow but thank you all for suggestions

[Tony Valko]

You're welcome. Thanks for the feedback!