+ Reply to Thread
Results 1 to 4 of 4

Finding Patterns in String of Text in One Column

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    16

    Finding Patterns in String of Text in One Column

    Hello Excel Forum,

    I am trying to figure out how to document and tabulate the patterns that might exist in a list of text. With the list of text below, if I set a parameter like the number of times the same 3 subjects repeat themselves or if I wanted to say the same time a pair repeat themselves, how would I do this?

    For example, I would like it to spit out something along the lines of:

    FHB F2H = 2
    FHB F2P = 1

    FHB
    F2P
    FNR
    FWH
    FWH
    F2H
    F2H
    FHB
    FHB
    FHB
    F2H
    F2H
    FNR
    FNR
    F2H
    F2H
    FHB
    FHB
    F2H
    FWH
    F2P
    F2P


    With this small set of data, there aren't any patterns where 3 subjects repeat but some of our data sets are significantly larger. The goal is to determine over a large data set, if the same subjects follow each other. All these subjects listed above are in order in which they interacted with the apparatus, but we want to know if the same two or three subjects followed each other.

    Let me know. This might not make a lot of sense so please, ask questions Thank you.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Patterns in String of Text in One Column

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Finding Patterns in String of Text in One Column

    Something like this?

    A B C D
    1 Pattern
    2 FHB FHB F2H 2
    3 F2P FHB F2P 1
    4 FNR
    5 FWH
    6 FWH
    7 F2H
    8 F2H
    9 FHB
    10 FHB
    11 FHB
    12 F2H
    13 F2H
    14 FNR
    15 FNR
    16 F2H
    17 F2H
    18 FHB
    19 FHB
    20 F2H
    21 FWH
    22 F2P
    23 F2P
    24

    Formula used.....
    In D2
    Please Login or Register  to view this content.
    Is this what you are trying to achieve?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    06-06-2014
    Posts
    16

    Re: Finding Patterns in String of Text in One Column

    Sktneer, this is the right idea except the formula is based on the fact that I can identify all the patterns upfront and just need to have them counted.

    However, I have a long lists of data with thousands of possible patterns. I can't possible write out all the patterns first, then simply count them. Imagine when I move to patterns of 3 or 4 subjects, the possible combinations get even greater when you have 25 unique subjects in the experiment. Therefore, I am looking for something that would identity patterns of 2, 3 or 4 as well as provide a count of how many times those patterns repeated themselves.

    Let me know if you have any suggestions for that.

+ 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] finding patterns in a range of cells
    By shart2k12 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-27-2013, 11:07 AM
  2. [SOLVED] Finding and Counting for text within a string in a column
    By WanderingDog in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-21-2013, 09:35 PM
  3. Finding patterns
    By GaidenFocus in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-06-2012, 03:59 PM
  4. [SOLVED] Finding text string within Range and returning Row/Column number
    By IanE78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2012, 12:13 PM
  5. Finding a text string in a column
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2010, 04:04 AM

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