Solved: Formula or vba required for most frequent text in an array.

[geedee65]

I'm looking for a formula that will return the most frequent text in an array.
In the worksheet 'Day', the daily wind direction is recorded in column B, with the date in column A.
In the 'Week' worksheet I would like to return the most frequent wind direction for that week, with the same in the 'Month' worksheet and the 'Year'.
Does anyone know how to do this with a formula (that preferably can be copied down as the real workbook has 10 years of data) or vba.

Also, normally with the rainfall totals and averages in the 'Week', 'Month' & 'Year' worksheets I would put a formula in a cell and select the required range from the 'Day' worksheet, but with a large amount of data this is very time consuming!!!!
Is there a formula that will achieve this, but can be copied down?
or is there a way to do this in vba?

I've attached the sample workbook.

[Bob Phillips]

.

[p45cal]

I went for a totally vba solution after struggling with formulae for a while. See attached.

We have very different results!

[geedee65]

Thanks xld & p45cal,

I'll go through both of them and get back to you if there are any parts I don't understand!

[geedee65]

p45cal,
how would I change your code to work on a worksheet_change event, so it updates automatically when new data is entered?

[p45cal]

right-click on the 'Day' sheet's tab and choose 'View code' and paste the following code at the flashing cursor:Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
WeekSheet
MonthSheet
YearSheet
End If
End SubNow the processing will take place every time a cell in column 3 (column C) of the Day sheet is changed. (You might want to delete the buttons.)
You will need to have entered column A data on each of the three other sheets. This column A data doesn't have to be in any special order on any of the 4 sheets.
Hope this does what you want,
p45cal
ps If you're pasting data in two or three columns to the Day sheet, you may have to re-enter a single value in column C to cause the processing to take place.

[geedee65]

thanks for that .........