Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-28-2014, 12:35 PM
tlamae tlamae is offline Counting Keywords and Phrases Windows 7 64bit Counting Keywords and Phrases Office 2010 64bit
Novice
Counting Keywords and Phrases
 
Join Date: May 2014
Posts: 3
tlamae is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 05-29-2014, 07:11 AM
gebobs gebobs is offline Counting Keywords and Phrases Windows 7 64bit Counting Keywords and Phrases Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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".
Reply With Quote
  #3  
Old 05-29-2014, 07:39 AM
tlamae tlamae is offline Counting Keywords and Phrases Windows 7 64bit Counting Keywords and Phrases Office 2010 64bit
Novice
Counting Keywords and Phrases
 
Join Date: May 2014
Posts: 3
tlamae is on a distinguished road
Default

I added an example in the excel file to further explain what I was trying to accomplish. Thanks
Attached Files
File Type: xlsx Keywords.xlsx (12.2 KB, 12 views)
Reply With Quote
  #4  
Old 05-29-2014, 07:55 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Counting Keywords and Phrases Windows 7 64bit Counting Keywords and Phrases Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 05-29-2014, 07:30 PM
whatsup whatsup is offline Counting Keywords and Phrases Windows 7 64bit Counting Keywords and Phrases Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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
Reply With Quote
  #6  
Old 05-30-2014, 06:49 AM
tlamae tlamae is offline Counting Keywords and Phrases Windows 7 64bit Counting Keywords and Phrases Office 2010 64bit
Novice
Counting Keywords and Phrases
 
Join Date: May 2014
Posts: 3
tlamae is on a distinguished road
Default

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
Reply With Quote
Reply

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
Counting Keywords and Phrases Identify certain text and move all phrases containing it down a line Chayes Word VBA 2 11-26-2013 01:16 PM
Counting Keywords and Phrases 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
Counting Keywords and Phrases Automatically Hyperlink phrases TinaIgnatiev Word 1 12-22-2010 01:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft