Thanks so much Paul.
I really do need a single formula to do it but again, I'm so impressed
with the assistance you and others have given me on this forum.
I've actually reorganised my base data as a workaround so the need has
passed but I've kept a copy of this code for future reference.
Thanks guys.
Kim
On Jan 7, 1:32 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> Do you care if it is done with a macro?
>
> Option Explicit
> Public Dict_Vals
> Sub CheckVals()
> Dim KeyVals, R, MaxVal, MaxCnt, RowCnt
> Set Dict_Vals = CreateObject("Scripting.Dictionary")
> Dict_Vals.RemoveAll
> RowCnt = Application.WorksheetFunction.CountA(Range("A1:A65000"))
> For R = 1 To RowCnt
> If (Dict_Vals.exists(Sheets("Sheet1").Cells(R, "A").Value)) Then
> Dict_Vals.Item(Cells(R, "A").Value) = Dict_Vals.Item(Cells(R,
> "A").Value) + 1
> Else
> Dict_Vals.Add (Cells(R, "A").Value), 1
> End If
> Next R
> MaxVal = 0
> MaxCnt = 0
> KeyVals = Dict_Vals.keys
> For R = 0 To UBound(KeyVals)
> If Dict_Vals.Item(KeyVals(R)) > MaxCnt Then
> MaxVal = KeyVals(R)
> MaxCnt = Dict_Vals.Item(KeyVals(R))
> Debug.Print "VAL: " & MaxVal & " Cnt: " & MaxCnt
> ElseIf Dict_Vals.Item(KeyVals(R)) = MaxCnt Then
> If KeyVals(R) > MaxVal Then
> MaxVal = KeyVals(R)
> MaxCnt = Dict_Vals.Item(KeyVals(R))
> End If
> End If
> Next R
> MsgBox "VAL: " & MaxVal & Chr(13) & "Cnt: " & MaxCnt
> End Sub
>
> Paul
>
> ________________________________
> From: Kim <anast...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Thu, January 6, 2011 1:16:15 AM
> Subject: Re: $$Excel-Macros$$ How do I find the highest of the most repeating
> values in a list of numbers ie. the max of the modes
>
> Thanks so much Ashish but the problem is in the temporary column. I
> really need to do it in one cell only without introducing any new
> data.
>
> Any idea on how I can achieve that?
>
> Again, thanks for your help.
>
> Kim
>
> On Jan 6, 4:07 pm, ashish koul <koul.ash...@gmail.com> wrote:
>
>
>
>
>
> > check the attachment see if it helps
>
> > On Thu, Jan 6, 2011 at 6:17 AM, Kim <anast...@gmail.com> wrote:
> > > Hi,
> > > I have a list of numbers and I need to find the highest of the most
> > > frequent occurrances.
>
> > > For example:
>
> > > List 1
> > > 1
> > > 3
> > > 3
> > > 4
> > > 4
>
> > > Should return the value 4 because its the highest of the most
> > > frequent
> > > occurrances of any number.
>
> > > List 2
> > > 1
> > > 3
> > > 3
> > > 3
> > > 4
> > > 4
>
> > > Should return 3 because it's the most frequent occurrance outright.
>
> > > I've tried using the mode command but it returns the first of the
> > > most
> > > frequent occurances but I want the highest of the most frequent
> > > occurrances.
>
> > > Any help would be greatly appreciated.
>
> > > Thanks
>
> > > Kim
>
> > > --
>
> >----------------------------------------------------------------------------------
>
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> > >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below link
> > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
> > *Regards*
> > * *
> > *Ashish Koul*
> > *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> > *akoul*.wordpress.com <http://akoul.wordpress.com/>
> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
> > P Before printing, think about the environment.
>
> > sample datat.xls
> > 24KViewDownload- Hide quoted text -
>
> > - Show quoted text -
>
> --
> ----------------------------------------------------------------------------------
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&...-
> Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
