+ Reply to Thread
Results 1 to 19 of 19

Transpose each word in sentence into individual cells/Count the repeat words...???

  1. #1
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Transpose each word in sentence into individual cells/Count the repeat words...???

    Hello,

    My objective is to...

    1. Take like a the words in a single cell.

    2. Put them into individual cells with a macro. (i.e. transposed to columns)

    -But the recorder does it with each of the words using arrays.
    (what if its a different sentence/paragraph) that I separate; The arrays would change.


    3. Then once each word is in it's own column, Take the whole Row, Make it into a column

    4. Then Some how count how many times each word appears.

    I tried doing this step by step but I am attempting to look for a cleaner way to do it.

    Attached you'll find a spread sheet and the paragraph in cell A1 is the paragraph I want to chop and count.

    Any assistance would be appreciated. Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    There's a workbook that tabulates phrase densities at https://app.box.com/s/ceurmq61hsdg8a6vn5nv
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    Perfect shg, Will Review and See If I can figure it out...

    I'll post it here once I conquer!

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,888

    Cool Try this :

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Try this :

    Quote Originally Posted by Marc L View Post
    Please Login or Register  to view this content.
    I have option explicit on...

    What would I declare T$ as?

    it won't allow me to declare it as anything...

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Try this :

    Quote Originally Posted by Marc L View Post
    Please Login or Register  to view this content.

    Can you break down what is happening here?

  7. #7
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    Quote Originally Posted by shg View Post
    There's a workbook that tabulates phrase densities at https://app.box.com/s/ceurmq61hsdg8a6vn5nv
    That's Freaking Bananas!
    Nice! Im surprised that macro doesn't count vowels hahaha

    Cool Stuff, thanks

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    You're welcome.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,888

    Cool See the mod and the other way :

    Quote Originally Posted by swade730 View Post
    I have option explicit on...

    What would I declare T$ as?

    it won't allow me to declare it as anything...

    Please Login or Register  to view this content.
    As you see, there is no Option Explicit in mine !
    T$ = means affect to variable T typed as String, like in VB … See String help.
    So try without Option Explicit

    Quote Originally Posted by swade730 View Post
    Can you break down what is happening here?
    The code counts each word via a Dictionary object (like the workbook link of shg)…

    A mod :
    Please Login or Register  to view this content.
    Another way :
    Please Login or Register  to view this content.
    Last edited by Marc L; 12-17-2013 at 09:52 PM.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,110

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    swade730

    Option Explicit.

    Good.

    Don't even try to copy the code that is not declare the variable properly.
    It will be risky sometime.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,888

    Lightbulb Tip :


    swade730, you can declare variables as written in my code !

    For example for DemoDictionary procedure : Dim T$, W

    Same as Dim T as String, W as Variant

    And last but not least, my codes work like a charm on computers with differents Excel versions.

    But I agree with jindon in general case, a good habit for a newbie to well organize his code …
    Last edited by Marc L; 12-17-2013 at 10:45 PM.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,110

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    Quote Originally Posted by swade730 View Post
    Im surprised that macro doesn't count vowels hahaha
    Here's an alternative.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,888

    Exclamation Words missing !


    jindon, a master blaster code for compound words like "NEW YORK Reuters"

    but word "week" is in duplicate and 10 little words are missing :

    a - as - been - in - it - its - of - on - the - to

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,110

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    They are excluded from the string for analysis.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,888

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???


    Yes I saw that in Pattern statment, a strain from regular expressions ?

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,110

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    OK, thanks for pointing that out.

    Here's a code
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    Quote Originally Posted by jindon View Post
    Here's an alternative.
    Please Login or Register  to view this content.

    This one works
    funny-Jim-Carrey-car-screaming.jpg

  18. #18
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???

    In an effort to better understand the code, I have added comments.

    the .property part of the code is most confusing. I've done some research and It's still not clear.

    Can anyone explain the .property aspect in simpler words please?
    And is "dic.keys" used to count the number of repeat words?


    Please Login or Register  to view this content.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,110

    Re: Transpose each word in sentence into individual cells/Count the repeat words...???


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help! Count how many words in a sentence match a dictionary of words
    By sonyaelis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2013, 05:31 AM
  2. [SOLVED] Copy, paste, transpose 4 cells delete and repeat
    By blarsen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-18-2012, 01:29 AM
  3. Replies: 6
    Last Post: 11-11-2009, 09:00 AM
  4. How to open a Word.doc using VBA and count the words in the word.doc?
    By eijoah in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2007, 12:58 AM
  5. Replies: 2
    Last Post: 01-23-2006, 02:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1