Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
most popular keyword
How to define the most popular keyword within one list?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
most popular keyword
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Tony_student" wrote: How to define the most popular keyword within one list? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
most popular keyword
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. -- Biff Microsoft Excel MVP "Tony_student" wrote in message ... How to define the most popular keyword within one list? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
most popular keyword
A few mo
=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),CO UNTIF(A1:A20,A1:A20),0)) (this is a CSE function) =INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0))) HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tony_student" wrote in message ... How to define the most popular keyword within one list? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IWC Aquatimer Watch, Popular Wristwatch | Excel Discussion (Misc queries) | |||
Most popular names in a list | Excel Discussion (Misc queries) | |||
Most popular items in a list | Excel Worksheet Functions |