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.

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)

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))

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

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

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

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.

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.

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

As shown here

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!