#1
|
|||
|
|||
Counting Keywords and Phrases
I need help with coming up with a formula that will return the number of times a word or phrase that is repeated in a column that will show the word and phrase including the number of times it was repeated.
I know there's a formula to look up words but I don't want to input what the word is. I want excel to return the words and phrases automatically. Is this possible? |
#2
|
|||
|
|||
If you could attach a sample sheet it may help.
I'm not sure at this point how Excel is going to pick out what words you consider "key". |
#3
|
|||
|
|||
I added an example in the excel file to further explain what I was trying to accomplish. Thanks
|
#4
|
||||
|
||||
Perhaps something like =SUMPRODUCT(--ISNUMBER(SEARCH("cary",$C$2:$C$30)))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Hi @all
Though it's possible to split phrases by excel functions you will spend a lot of time (and space on your worksheet) to get everything done, whereas a couple of lines of vba-code do the trick - and it's written in few minutes. Just as idea: Code:
Sub Do_It() Dim arr As Variant Dim arrSplit As Variant Dim vntItem As Variant Dim objphrase As Object Dim objkeys As Object Dim lngItem As Long, lngkeys As Long Set objphrase = CreateObject("scripting.dictionary") Set objkeys = CreateObject("scripting.dictionary") 'Get Data of Column C With ActiveSheet arr = .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row).Value End With For lngItem = 1 To UBound(arr, 1) 'just in case... arr(lngItem, 1) = Trim(CStr(arr(lngItem, 1))) If Not arr(lngItem, 1) = "" Then 'makes things easier ... arr(lngItem, 1) = " " & arr(lngItem, 1) & " " 'Collect and count phrases If objphrase.Exists(arr(lngItem, 1)) Then objphrase(arr(lngItem, 1)) = objphrase(arr(lngItem, 1)) + 1 Else objphrase(arr(lngItem, 1)) = 1 End If 'extract keys arrSplit = Split(arr(lngItem, 1), " ") 'Collect and count keys For lngkeys = 1 To UBound(arrSplit) - 1 If objkeys.Exists(arrSplit(lngkeys)) Then objkeys(arrSplit(lngkeys)) = objkeys(arrSplit(lngkeys)) + 1 Else objkeys(arrSplit(lngkeys)) = 1 End If Next lngkeys End If Next lngItem 'Report With ActiveSheet If objkeys.Count > 0 Then lngItem = 0 ReDim arr(1 To objkeys.Count, 1 To 2) For Each vntItem In objkeys lngItem = lngItem + 1 arr(lngItem, 1) = vntItem arr(lngItem, 2) = objkeys(vntItem) Next vntItem .Cells(2, 5).Resize(UBound(arr, 1), 2).Value = arr End If If objphrase.Count > 0 Then lngItem = 0 ReDim arr(1 To objphrase.Count, 1 To 2) For Each vntItem In objphrase lngItem = lngItem + 1 arr(lngItem, 1) = vntItem arr(lngItem, 2) = objphrase(vntItem) Next vntItem .Cells(2, 7).Resize(UBound(arr, 1), 2).Value = arr End If End With 'Clean up Set objkeys = Nothing Set objphrase = Nothing End Sub |
#6
|
|||
|
|||
Omg Whatsup!!! You are awesome! You just don't know how much you have really helped me. I think I am gonna take a class on VBA. Again, thank you so much
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I find Repeating Words/Phrases? | CCD2016 | PowerPoint | 0 | 12-01-2013 09:37 PM |
Identify certain text and move all phrases containing it down a line | Chayes | Word VBA | 2 | 11-26-2013 01:16 PM |
Selecting text between two key phrases | Chayes | Word VBA | 6 | 06-24-2012 06:54 PM |
Updating Hyperlinks with svn keywords | ehsansad | Word | 0 | 04-28-2011 03:14 AM |
Automatically Hyperlink phrases | TinaIgnatiev | Word | 1 | 12-22-2010 01:42 PM |