most popular keyword

[Tony_student]

How to define the most popular keyword within one list?

 

[ryguy7272]

This should do it for you:

Sub Count1()
Dim lRow As Long, i As Long, j As Long
Dim C As Range, Lst As Range

lRow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & lRow)

For Each C In Lst
If C.Value = "" Then GoTo skip
C.Offset(0, 1).Value = 1
If C.Row = 1 Then GoTo skip
If C.Value = C.Offset(-1, 0).Value Then
C.Offset(0, 1).Value = C.Offset(-1, 1).Value + 1
End If
skip:
Next C
End Sub


Sub Count2()
Dim lRow As Long, i As Long, j As Long
Dim C As Range, Lst As Range

lRow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & lRow)

For Each C In Lst
If C.Value = "" Then GoTo skip
C.Offset(0, 1).Value = 1
If C.Row = 1 Then GoTo skip
If C.Value = C.Offset(-1, 0).Value Then
C.Offset(0, 1).Value = C.Offset(-1, 1).Value + 1
End If
skip:
Next C
End Sub

HTH,
Ryan---

 

[T. Valko]

Try this array formula** :

Assumes no empty cells within the range. If there are no duplicates the
formula returns #N/A.

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,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.

 

[ryguy7272]

A few more:
=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20),0))
(this is a CSE function)

=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))

HTH,
Ryan---