+ Reply to Thread
Results 1 to 15 of 15

finding patterns in a range of cells

  1. #1
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    finding patterns in a range of cells

    Attached, please find excel file to show data.
    I have a variety of patterns in cells B:G as follows:
    0 1 0 1 0 0
    0 0 0 0 0 1
    1 0 0 0 1 1
    0 1 0 1 0 0
    etc..
    Need help with a vba to find patterns and list them separately in new cells (L:Q) and their frequencies, as indicated in the file.
    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: finding patterns in a range of cells

    Hi,

    How do you get 22 occurrences of the 000001 pattern. I can only see 13

    Wouldn't it be simpler to concatenate B3:G3 into say A3, copy it down. Do the same with L4:Q4 in say K4, then in R4 use

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: finding patterns in a range of cells

    Why don't you just use a formula

    =COUNTIFS(B3:B317,L4,C3:C317,M4,D3:D317,N4,E3:E317,O4,F3:F317,P4,G3:G317,Q4)

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: finding patterns in a range of cells

    Quote Originally Posted by Richard Buttrey View Post
    Wouldn't it be simpler to concatenate B3:G3 into say A3, copy it down. Do the same with L4:Q4 in say K4, then in R4 use

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    You can do it without the helpers

    =SUMPRODUCT(--($B$3:$B$317&$C$3:$C$317&$D$3:$D$317&$E$3:$E$317&$F$3:$F$317&$G$3:$G$317=$L$4&M4&N4&O4&P4&Q4))

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: finding patterns in a range of cells

    ...just seen the requirement to list the unique values.

    So using my previous suggestion copy the column A values into Column K. Then select the column K range and use the 'Remove Duplicates' functionality in the Data Tools area of the ribbon.

    Then copy R4 down as necessary

  6. #6
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: finding patterns in a range of cells

    How do you index them and list most frequent pattern first, then second, etc..

  7. #7
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: finding patterns in a range of cells

    it is just an example... I did not actually count them.. sorry!

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: finding patterns in a range of cells

    Hi,

    Not sure what you mean by index them but you could sort the results by frequency to get an ordered list in descending order. Is that what you mean?

  9. #9
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: finding patterns in a range of cells

    Sorting the results gives the entire list, so if you have 1000 lines you have to go through all of them. In this example after applying the formula, there are 32 distinct patterns, I just want to list the pattern and its frequency only once, e.g.:
    0000000 32
    1000000 20
    and so on. Sorting will list'000000' 32 times then '100000' 20 times, then the next, etc.
    Thanks

  10. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: finding patterns in a range of cells

    Hi,

    I don't understand your difficulty.
    I didn't say to sort the original data

    If you've followed my method you will end up in the results column K with (say) 32 unique patterns (with the original columns L:Q) and column R with the frequency. So just sort K:R using column R as the sort key in descending order.

  11. #11
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: finding patterns in a range of cells

    I used "=SUMPRODUCT(--($B$4:$B$318&$C$4:$C$318&$D$4:$D$318&$E$4:$E$318&$F$4:$F$318&$G$4:$G$318=$B$4&C4&D4&E4&F4&G4))" in column 'I' and I sorted the six columns using 'I' the sort key. in the example the pattern in columns L:P is not known.

  12. #12
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: finding patterns in a range of cells

    Quote Originally Posted by shart2k12 View Post
    Sorting the results gives the entire list, so if you have 1000 lines you have to go through all of them. In this example after applying the formula, there are 32 distinct patterns, I just want to list the pattern and its frequency only once, e.g.:
    0000000 32
    1000000 20
    and so on. Sorting will list'000000' 32 times then '100000' 20 times, then the next, etc.
    Thanks
    There are only 64 variations, list them all, then use Richard's or my original suggestion , and you will get what you want, including the sequencesthat have no matches.

  13. #13
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: finding patterns in a range of cells

    thanks, I am trying vba on my own.. no luck so far..

  14. #14
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: finding patterns in a range of cells

    As shown here
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-03-2013
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: finding patterns in a range of cells

    Thank you Bob for your help. When I run it I get an error at :With Range("C1").Resize(.Count, 2)
    I am running excel 2013, if that makes any difference.
    But that is what i am looking for. Again thanks and Happy holidays!

+ 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. Finding patterns
    By GaidenFocus in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-06-2012, 03:59 PM
  2. Replies: 3
    Last Post: 04-01-2012, 05:37 AM
  3. Macro to search for patterns in a range of dates
    By bsweet0us in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2011, 10:49 PM
  4. Finding same patterns in groupings of cells
    By modoud in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-19-2007, 01:19 PM
  5. [SOLVED] Patterns in cells won't print
    By Gabrielle in forum Excel General
    Replies: 2
    Last Post: 04-21-2006, 09:25 AM

Tags for this Thread

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