Results 1 to 12 of 12

Thread: Excel: how to count frequency of words across multiple rows and columns?

  1. #1
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Question Excel: how to count frequency of words across multiple rows and columns?

    heyo

    data is in (eg) 3 columns and 10 rows.
    There is repetition of words across all cells

    I need to work out which word is most frequent, plus the less frequent - listed.

    eg how many times does cat / dog / log etc occur across all these cells (not simply in a partic column)?

    Have looked through functions / tried all sorts, but not finding the solution. Found the frequency function, but that only works for numbers.

    TIA
    One can never stop saying Thank You

  2. #2
    Nothing runs like a Deere cotswoldcs's Avatar
    Join Date
    Mar 2004
    Location
    Bang in the heart of the cotswolds
    Posts
    793
    Thanks
    40
    Thanked
    18 times in 18 posts
    • cotswoldcs's system
      • Motherboard:
      • Asus P4C800-E Deluxe
      • CPU:
      • Pentium 4 3.0Ghz Northwood (@3.4Ghz on water)
      • Memory:
      • 1.5Gb Corsair TwinX PC3200/PC3700
      • Storage:
      • 150Gb Raptor
      • Graphics card(s):
      • XFX 6600GT 128Mb (w waterblock)
      • PSU:
      • Seasonic S12-380
      • Case:
      • Antec Sonata I
      • Monitor(s):
      • 3 x Iiyama 19" LCD (5:4) on Comrac Tripple Mount
      • Internet:
      • PlusNet 1.6Mb ADSL

    Re: Excel: how to count frequency of words across multiple rows and columns?

    I think this can be done with a PivotTable and the count function. If you've got multiple words in each cell this makes it a little more complicated

    I'm sure there are people more proficient in excel but I like a challenge. Can you send me the file to play with?

  3. #3
    One Great Ordeal!
    Join Date
    Feb 2005
    Posts
    1,764
    Thanks
    201
    Thanked
    56 times in 49 posts

    Re: Excel: how to count frequency of words across multiple rows and columns?

    You can use a countif for each single occurance of a word you want to find assuming your data starts in column A row 1 and ends in column C row 10:

    =Countif(a1:c10,"Cat")

    I'd define the range in Name Manager myself personally, the above would become:

    =Countif(rangename,"Cat")

    Repeat for each word you want to find and you should be sorted.. You can then use a sort to list the numbers correctly starting with the highest to the lowest

    I'd also stick the analysis of the data on another sheet just to keep the original data and the analysis seperate.
    Last edited by digit; 15-07-2009 at 10:22 PM.

  4. #4
    Nothing runs like a Deere cotswoldcs's Avatar
    Join Date
    Mar 2004
    Location
    Bang in the heart of the cotswolds
    Posts
    793
    Thanks
    40
    Thanked
    18 times in 18 posts
    • cotswoldcs's system
      • Motherboard:
      • Asus P4C800-E Deluxe
      • CPU:
      • Pentium 4 3.0Ghz Northwood (@3.4Ghz on water)
      • Memory:
      • 1.5Gb Corsair TwinX PC3200/PC3700
      • Storage:
      • 150Gb Raptor
      • Graphics card(s):
      • XFX 6600GT 128Mb (w waterblock)
      • PSU:
      • Seasonic S12-380
      • Case:
      • Antec Sonata I
      • Monitor(s):
      • 3 x Iiyama 19" LCD (5:4) on Comrac Tripple Mount
      • Internet:
      • PlusNet 1.6Mb ADSL

    Re: Excel: how to count frequency of words across multiple rows and columns?

    OK been playing with Excel and if you use the Data -> Text to columns... function you can convert a cell with multiple words into cells with a single word in each. If you assigned a reference for each of these cells eg: A1, B2, C3, etc you should be able to use a PivotTable on the data and count the values from there

    Hmm, wish I was better at Excel - it's amazingly powerful yet not very intuitive!

  5. #5
    One Great Ordeal!
    Join Date
    Feb 2005
    Posts
    1,764
    Thanks
    201
    Thanked
    56 times in 49 posts

    Re: Excel: how to count frequency of words across multiple rows and columns?

    Is it one word per cell we are talking about or multiple words in each cell? If the latter my method won't work ...

  6. #6
    Nothing runs like a Deere cotswoldcs's Avatar
    Join Date
    Mar 2004
    Location
    Bang in the heart of the cotswolds
    Posts
    793
    Thanks
    40
    Thanked
    18 times in 18 posts
    • cotswoldcs's system
      • Motherboard:
      • Asus P4C800-E Deluxe
      • CPU:
      • Pentium 4 3.0Ghz Northwood (@3.4Ghz on water)
      • Memory:
      • 1.5Gb Corsair TwinX PC3200/PC3700
      • Storage:
      • 150Gb Raptor
      • Graphics card(s):
      • XFX 6600GT 128Mb (w waterblock)
      • PSU:
      • Seasonic S12-380
      • Case:
      • Antec Sonata I
      • Monitor(s):
      • 3 x Iiyama 19" LCD (5:4) on Comrac Tripple Mount
      • Internet:
      • PlusNet 1.6Mb ADSL

    Re: Excel: how to count frequency of words across multiple rows and columns?

    Digit - You could split it using the method I mentioned above and then use the Countif function. That would work.

  7. #7
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Excel: how to count frequency of words across multiple rows and columns?

    multiple words per cell. But that could potentially be sorted, so let's focus on single words per cell first.

    I know the countif function well, as well as pivot tables, D functions etc. Was playing with them before I posted the question, and it doesn't work. Countif would require use of wildcards to work properly (which it handles) but the real problem is that one has to have established what words one wants to search for first, which kinda defeats the object.

    In essence, I need to simply ask Excel which words are most frequent (without having a clue what they are) and it tells me.



    The pivots don't help AFAIK because one needs to work across columns, whereas pivots keep data within columns....
    One can never stop saying Thank You

  8. #8
    Nothing runs like a Deere cotswoldcs's Avatar
    Join Date
    Mar 2004
    Location
    Bang in the heart of the cotswolds
    Posts
    793
    Thanks
    40
    Thanked
    18 times in 18 posts
    • cotswoldcs's system
      • Motherboard:
      • Asus P4C800-E Deluxe
      • CPU:
      • Pentium 4 3.0Ghz Northwood (@3.4Ghz on water)
      • Memory:
      • 1.5Gb Corsair TwinX PC3200/PC3700
      • Storage:
      • 150Gb Raptor
      • Graphics card(s):
      • XFX 6600GT 128Mb (w waterblock)
      • PSU:
      • Seasonic S12-380
      • Case:
      • Antec Sonata I
      • Monitor(s):
      • 3 x Iiyama 19" LCD (5:4) on Comrac Tripple Mount
      • Internet:
      • PlusNet 1.6Mb ADSL

    Re: Excel: how to count frequency of words across multiple rows and columns?

    Use the File -> Print function in Excel, and take the printout to a primary school and ask the kids to cut up the paper into individual words. Tell them it's a fun game and they have to collect all the words that are the same together and find the most frequent...

    sorry probably not helpful

  9. #9
    Senior Member MaddAussie's Avatar
    Join Date
    Dec 2006
    Location
    Deepest Darkest Dorset
    Posts
    1,708
    Thanks
    628
    Thanked
    297 times in 179 posts
    • MaddAussie's system
      • Motherboard:
      • Asus ROG Strix Z370G
      • CPU:
      • i7 8700k (5.1Ghz)
      • Memory:
      • 16Gb
      • Storage:
      • 500G 960 EVO NMVE
      • Graphics card(s):
      • GTX 1070
      • PSU:
      • Corsair RM650i
      • Case:
      • Corsair Carbide A1r 240
      • Operating System:
      • Win 10
      • Monitor(s):
      • 24" Dell HD + Samsung HD

    Re: Excel: how to count frequency of words across multiple rows and columns?

    I think you would have to write some code in VB to do this as im not sur that Excel has any capability for this built in

  10. #10
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Excel: how to count frequency of words across multiple rows and columns?

    I concur with MaddAussie - if you can't predefine which words you're looking for you'll need to use VB to iterate over the range of cells, split the content into words, and then use a data structure of some sort to tally the words as you find them. All of which you can do *within* Excel, of course, but not with just Excel functions...

  11. #11
    Senior Member
    Join Date
    Jul 2009
    Location
    West Sussex
    Posts
    1,616
    Thanks
    165
    Thanked
    227 times in 208 posts
    • kompukare's system
      • Motherboard:
      • Asus P8Z77-V LX
      • CPU:
      • Intel i5-3570K
      • Memory:
      • 4 x 8GB DDR3
      • Storage:
      • Samsung 850 EVo 500GB | Corsair MP510 960GB | 2 x WD 4TB spinners
      • Graphics card(s):
      • Sappihre R7 260X 1GB (sic)
      • PSU:
      • Antec 650 Gold TruePower (Seasonic)
      • Case:
      • Aerocool DS 200 (silenced, 53.6 litres)l)
      • Operating System:
      • Windows 10-64
      • Monitor(s):
      • 2 x ViewSonic 27" 1440p

    Re: Excel: how to count frequency of words across multiple rows and columns?

    One thing the OP didn't mention is whether this is a one-off task or something which needs to be done on a regular basis.

    If on a regular basis, there's certainly no substitute for VBA. Coding VBA isn’t usually too hard but I’m not going to go into it here.

    But if it's only a once off task, I would take the data and do something along these lines (I am assuming Multiple Words in Multiple Cells here):
    • Export your worksheet to .CSV
    • Import that text file into Word (or open in Notepad first and paste into Word).
    • Now replace all word separators by ^p (Word's paragraph mark - the idea is to make a very long single column list). Do the same for commas. Hopefully your words are all separated by spaces but if some are separated by something else you can replace those by ^p too. Also, you might want to replace any punctuation by <NOTHING>
    • You should now have a long document with only one word per line. Save this as .CSV or just paste the whole thing into a blank Excel worksheet.
    • Then Data>Sort that column and delete any lines which are full of punctuation marks etc.
    • Okay, now you have all your words in column A with one word per cell. If in cell B you now enter something like this:
    • =COUNTIF(A$1:A$200,A1)
    • BTW you will have to change the A$200 to reflect the length of your list.
    • Almost there, now select all of column B and go to C1 and Edit>Paste Special>Paste Values.
    • If you sort the whole worksheet by column C you will get the frequencies.

    Hope that helps.

  12. Received thanks from:

    fuddam (17-07-2009)

  13. #12
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Excel: how to count frequency of words across multiple rows and columns?

    thanks for all the replies

    I have the kids cutting as we speak



    oh, and it's something that needs to be done weekly / frequently, so thanks for the idea Kompkare, but as some of you have said, looks like a job for VBA...
    One can never stop saying Thank You

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •