Fin the most frequently Cell in a Row

[Kendor]

Now hi...

i got a little problem in my VB Script...
i have to find the most frequently cell (string) in a single row:

ex:
( | x | <-- a cell)

| Pete | Pete| Tom | Pete | Joe | Joe | Joe | Pete | Frank |

=> Pete 4 times; Tom 2 times; Joe 3 times; Frank 1
function returns: "Pete"

the problem is, that i don't really know how many cells in a row ther
are...
i tried to solve it like this, but this doesn't work out... (i als
know why... and now searching for another solution...)

 

[William]

Go to Chip Pearson's site and look at....
"Most Common String In A Range"
http://www.cpearson.com/excel/excelF.htm
--
XL2002
Regards

William

(e-mail address removed)

| Now hi...
|
| i got a little problem in my VB Script...
| i have to find the most frequently cell (string) in a single row:
|
| ex:
| ( | x | <-- a cell)
|
| | Pete | Pete| Tom | Pete | Joe | Joe | Joe | Pete | Frank |
|
| => Pete 4 times; Tom 2 times; Joe 3 times; Frank 1
| function returns: "Pete"
|
| the problem is, that i don't really know how many cells in a row there
| are...
| i tried to solve it like this, but this doesn't work out... (i also
| know why... and now searching for another solution...)
|
| >
| > Private Function Referenzstring() As String
| >
| > Dim zaehler(1 To 100) As Integer
| > Dim werte(1 To 100) As String
| > Dim i As Integer
| > Dim j As Integer
| > Dim addcounter As Integer
| > Dim Count As Integer
| >
| > werte(1) = Worksheets(1).Cells(CurrentRow, 1).FormulaLocal
| > addcounter = 1
| > zaehler(1) = 1
| >
| > 'doesn't workk.. other solution required...
| > For j = 1 To intSpaltenCount - 1
| > For i = 1 To intSpaltenCount - j
| > If StrComp(Worksheets(1).Cells(CurrentRow, i +
| > j).FormulaLocal, werte(j)) Then
| > zaehler(j) = zaehler(j) + 1
| > Else
| > addcounter = addcounter + 1
| > zaehler(i + j) = zaehler(i + j) + 1
| > werte(i + j) = Worksheets(1).Cells(CurrentRow, i +
| > j).FormulaLocal
| > End If
| > Next i
| > Next j
| > #
| >
| > i = 1
| > For j = 2 To addcounter
| > If zaehler(i) < zaehler(j) Then
| > i = j
| > End If
| > Next j
| >
| > Referenzstring = werte(i)
| >
| > End Function
| >
|
|
| ---
| Message posted
|

 

[Kendor]

ok thanks...


now, how to port this to my vba script?

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0)

 

[Tim]

i know its not VB, but might it be easier to use a pivot table?

 

[Kendor]

any suggestion

 

[Paul Lautman]

Well assuming that your range (row) is called rng then something like:
with application.worksheetfunction
Referenzstring =
..INDEX(range("Rng"),.MATCH(.MAX(.COUNTIF(range("Rng"),range("Rng"))),.COUNTI
F(range("Rng"),range("Rng")),0))
end with

This is untested and may need tweeking (particularly the use of the range()
property.

 

[Tom Ogilvy]

From Chip's page:

the following **array formula** will return the most frequently used entry

in a range:

Using your approach will not treat the formula as an array formula and will
therefore not work.

You could use Evaluate:

Sub AAABBBCCC()
Dim sStr as String, vVar as variant
sStr = "INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))"
sStr = Application.Substitute(sStr, "Rng", "A1:A200")
vVAr = Evaluate(sStr)
MsgBox vVAr
End Sub



--
Regards,
Tom Ogilvy

Well assuming that your range (row) is called rng then something like:
with application.worksheetfunction
Referenzstring =
..INDEX(range("Rng"),.MATCH(.MAX(.COUNTIF(range("Rng"),range("Rng"))),.COUNTI
F(range("Rng"),range("Rng")),0))
end with

This is untested and may need tweeking (particularly the use of the range()
property.