# finding patterns in a range of cells

1. ## 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.

2. ## 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:
`Please Login or Register  to view this content.`

3. ## 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. ## Re: finding patterns in a range of cells

Originally Posted by Richard Buttrey
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:
`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. ## 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. ## Re: finding patterns in a range of cells

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

7. ## Re: finding patterns in a range of cells

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

8. ## 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. ## 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. ## Re: finding patterns in a range of cells

Hi,

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. ## 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. ## Re: finding patterns in a range of cells

Originally Posted by shart2k12
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. ## Re: finding patterns in a range of cells

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

14. ## Re: finding patterns in a range of cells

As shown here

15. ## 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!

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