+ Reply to Thread
Results 1 to 6 of 6

Makro/Formula to count number of consecutive particular words in a list of random words

  1. #1
    Registered User
    Join Date
    09-02-2014
    Location
    Web
    MS-Off Ver
    2010
    Posts
    3

    Makro/Formula to count number of consecutive particular words in a list of random words

    Hi,

    I'd appreciate any help on how to solve the following issue:
    I have a binary set of words (lets say TRUE and FALSE) and want to count the number of consecutive "TRUE" words in a column.

    E.g.

    TRUE
    TRUE
    FALSE
    TRUE
    FALSE
    FALSE
    FALSE
    TRUE
    TRUE
    TRUE

    And the macro or formula should tell me the following in a separate column:
    2
    1
    3

    Thanks in advance.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Makro/Formula to count number of consecutive particular words in a list of random word

    Hi,

    Are those "words" text entries, or genuine Boolean TRUE/FALSE responses?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    09-02-2014
    Location
    Web
    MS-Off Ver
    2010
    Posts
    3

    Re: Makro/Formula to count number of consecutive particular words in a list of random word

    Hi,

    they are boolean true/false responses from a separate set of values. I wanted to keep it simple but I basically need the number of consecutive values that are below the mean of a column.

    something like this:


    7.7 TRUE
    7.7 TRUE
    7.7 TRUE
    7.7 TRUE
    10.78 FALSE
    9.24 FALSE
    10.78 FALSE
    10.78 FALSE
    9 TRUE
    9.5 FALSE
    7 TRUE

    Result:
    4
    1
    1

    Best

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Makro/Formula to count number of consecutive particular words in a list of random word

    May as well perform the operation on the Booleans since you've already derived them.

    Based on those Booleans being in B1:B10, first go to Name Manager (Formulas tab) and create the following:

    Name: Arry1
    Refers to: =FREQUENCY(IF($B$1:$B$10,ROW($B$1:$B$10)),IF(1-$B$1:$B$10,ROW($B$1:$B$10)))

    Exit Name Manager.

    Then enter this array formula** in C1:

    =SUM(IF(Arry1,1))

    Then enter this array formula** in D1:

    =IF(ROWS($1:1)>$C$1,"",INDEX(Arry1,SMALL(IFERROR(IF(Arry1,ROW($B$1:$B$10)),""),ROWS($1:1))))

    Copy this formula down (though not the one in C1) until you start to get blanks for the results.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  5. #5
    Registered User
    Join Date
    09-02-2014
    Location
    Web
    MS-Off Ver
    2010
    Posts
    3

    Re: Makro/Formula to count number of consecutive particular words in a list of random word

    Works.

    Thank you!

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Makro/Formula to count number of consecutive particular words in a list of random word

    You're welcome!

+ 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. [SOLVED] Unique Random Words From Word List
    By uniks in forum Excel General
    Replies: 5
    Last Post: 03-03-2014, 06:42 AM
  2. How to create random sentences out of a list of words
    By MORACH87 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-21-2013, 04:25 AM
  3. 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
  4. Replies: 3
    Last Post: 11-28-2012, 04:00 AM
  5. Count Consecutive Repeating Words
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-16-2010, 02:37 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