Hi Everyone,

I have a Worksheet with Combinations in the Format :-

01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
etc

The Combinations are on a Worksheet Named "Combinations" and go from
A1:P65000 for example. Each Combination is in a SINGLE CELL.

What I would like to be able to do is to COUNT how many times the
following Combinations Appear in the Worksheet Named "Combinations" :-

01-02-03
03-05-08
01-02-03-04
03-04-05-08
01-02-03-04-05
04-05-06-07-08
01-02-03-04-05-06
etc

There could be as many as 30 or so to Check, Ranging from 3 Numbers up
to 6 Numbers.
The Combinations to Check will be in a Range on a Worksheet Named
"Results" in A1:A30 for example.
Is there a Formula I could use to achieve this or will it need to be
done using VB?.

Thanks very much in Advance.
Paul

Suggested is the following:-

Sub CountCombins()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim c As Range

Set ws1 = Worksheets("Results")
Set ws2 = Worksheets("Combinations")
With ws1
Set rng1 = Range(.Range("A1"), .Range("A1").End(xlDown))
End With

Set rng2 = ws2.Range("A1:P65000")

For Each c In rng1
c(1, 2) = Application.CountIf(rng2, c.Value)
Next

End Sub

Regards,
Greg


-----Original Message-----
Hi Everyone,

I have a Worksheet with Combinations in the Format :-

01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
etc

The Combinations are on a Worksheet Named "Combinations"
and go from
A1:P65000 for example. Each Combination is in a SINGLE
CELL.

What I would like to be able to do is to COUNT how many
times the
following Combinations Appear in the Worksheet
Named "Combinations" :-

01-02-03
03-05-08
01-02-03-04
03-04-05-08
01-02-03-04-05
04-05-06-07-08
01-02-03-04-05-06
etc

There could be as many as 30 or so to Check, Ranging from
3 Numbers up
to 6 Numbers.
The Combinations to Check will be in a Range on a
Worksheet Named
"Results" in A1:A30 for example.
Is there a Formula I could use to achieve this or will it
need to be
done using VB?.

Thanks very much in Advance.
Paul
.

On sheet results, in cell b1 put in the formula

=countif(Combinations!$A$1:$P$65000,"*"&$A1&"*")

then drag fill down to B30.

The probability of winning the lottery is 1/total number of possible
combinations

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Hi Everyone,

I have a Worksheet with Combinations in the Format :-

01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
etc

The Combinations are on a Worksheet Named "Combinations" and go from
A1:P65000 for example. Each Combination is in a SINGLE CELL.

What I would like to be able to do is to COUNT how many times the
following Combinations Appear in the Worksheet Named "Combinations" :-

01-02-03
03-05-08
01-02-03-04
03-04-05-08
01-02-03-04-05
04-05-06-07-08
01-02-03-04-05-06
etc

There could be as many as 30 or so to Check, Ranging from 3 Numbers up
to 6 Numbers.
The Combinations to Check will be in a Range on a Worksheet Named
"Results" in A1:A30 for example.
Is there a Formula I could use to achieve this or will it need to be
done using VB?.

Thanks very much in Advance.
Paul

Greg :-
Thanks for the Macro, it ran to completion, but unfortunately produced
zeros starting at cell b1 to the end.

Tom :-
Your Formula worked like a dream.

I would like to be able to use a Macro for this task as calculating
using Formulas seem to take a considerable time, I assume that
processing time would be far faster using VB.

Thank you both very much for answering my query.

All the very best
Paul



"Tom Ogilvy" wrote in message ...
On sheet results, in cell b1 put in the formula

=countif(Combinations!$A$1:$P$65000,"*"&$A1&"*")

then drag fill down to B30.

The probability of winning the lottery is 1/total number of possible
combinations

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Hi Everyone,

I have a Worksheet with Combinations in the Format :-

01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
etc

The Combinations are on a Worksheet Named "Combinations" and go from
A1:P65000 for example. Each Combination is in a SINGLE CELL.

What I would like to be able to do is to COUNT how many times the
following Combinations Appear in the Worksheet Named "Combinations" :-

01-02-03
03-05-08
01-02-03-04
03-04-05-08
01-02-03-04-05
04-05-06-07-08
01-02-03-04-05-06
etc

There could be as many as 30 or so to Check, Ranging from 3 Numbers up
to 6 Numbers.
The Combinations to Check will be in a Range on a Worksheet Named
"Results" in A1:A30 for example.
Is there a Formula I could use to achieve this or will it need to be
done using VB?.

Thanks very much in Advance.
Paul

Paul,

I interpreted your post wrong. Mine is designed only to
count matches when the value in sheet Results matches in
whole the values in sheet Combinations as opposed to when
it forms only part of the cell values. As for the relative
performance of VBA versus worksheet formulas, formulas
are, at least theoretically, faster. However, the main
feature of my macro simply employs the CountIf formula, so
the difference should be minor. My assumption is that my
macro seemed faster because it found no matches and this
is very simple for the CountIf formula to determine.

You may prefer a macro becasue it lets you control the
calculation better. I incorporated Tom's idea of using the
asteriscs to find partial matches. Kudos to Tom.

Sub CountCombins()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim c As Range

Set ws1 = Worksheets("Results")
Set ws2 = Worksheets("Combinations")
With ws1
Set rng1 = Range(.Range("A1"), .Range("A1").End(xlDown))
End With

Set rng2 = ws2.Range("A1:P65000")

For Each c In rng1
c(1, 2) = Application.CountIf(rng2, "*" & c & "*")
Next

End Sub

Regards,
Greg


-----Original Message-----
Greg :-
Thanks for the Macro, it ran to completion, but
unfortunately produced
zeros starting at cell b1 to the end.

Tom :-
Your Formula worked like a dream.

I would like to be able to use a Macro for this task as
calculating
using Formulas seem to take a considerable time, I assume
that
processing time would be far faster using VB.

Thank you both very much for answering my query.

All the very best
Paul



"Tom Ogilvy" wrote in message
...
On sheet results, in cell b1 put in the formula

=countif(Combinations!$A$1:$P$65000,"*"&$A1&"*")

then drag fill down to B30.

The probability of winning the lottery is 1/total
number of possible
combinations

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Hi Everyone,

I have a Worksheet with Combinations in the Format :-

01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
etc

The Combinations are on a Worksheet
Named "Combinations" and go from
A1:P65000 for example. Each Combination is in a
SINGLE CELL.

What I would like to be able to do is to COUNT how
many times the
following Combinations Appear in the Worksheet
Named "Combinations" :-

01-02-03
03-05-08
01-02-03-04
03-04-05-08
01-02-03-04-05
04-05-06-07-08
01-02-03-04-05-06
etc

There could be as many as 30 or so to Check, Ranging
from 3 Numbers up
to 6 Numbers.
The Combinations to Check will be in a Range on a
Worksheet Named
"Results" in A1:A30 for example.
Is there a Formula I could use to achieve this or
will it need to be
done using VB?.

Thanks very much in Advance.
Paul
.

Hi Greg,

Thanks for the Updated Macro, it works great.

All the Best
Paul



"Greg Wilson" wrote in message ...
Paul,

I interpreted your post wrong. Mine is designed only to
count matches when the value in sheet Results matches in
whole the values in sheet Combinations as opposed to when
it forms only part of the cell values. As for the relative
performance of VBA versus worksheet formulas, formulas
are, at least theoretically, faster. However, the main
feature of my macro simply employs the CountIf formula, so
the difference should be minor. My assumption is that my
macro seemed faster because it found no matches and this
is very simple for the CountIf formula to determine.

You may prefer a macro becasue it lets you control the
calculation better. I incorporated Tom's idea of using the
asteriscs to find partial matches. Kudos to Tom.

Sub CountCombins()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim c As Range

Set ws1 = Worksheets("Results")
Set ws2 = Worksheets("Combinations")
With ws1
Set rng1 = Range(.Range("A1"), .Range("A1").End(xlDown))
End With

Set rng2 = ws2.Range("A1:P65000")

For Each c In rng1
c(1, 2) = Application.CountIf(rng2, "*" & c & "*")
Next

End Sub

Regards,
Greg


-----Original Message-----
Greg :-
Thanks for the Macro, it ran to completion, but
unfortunately produced
zeros starting at cell b1 to the end.

Tom :-
Your Formula worked like a dream.

I would like to be able to use a Macro for this task as
calculating
using Formulas seem to take a considerable time, I assume
that
processing time would be far faster using VB.

Thank you both very much for answering my query.

All the very best
Paul



"Tom Ogilvy" wrote in message
...
On sheet results, in cell b1 put in the formula

=countif(Combinations!$A$1:$P$65000,"*"&$A1&"*")

then drag fill down to B30.

The probability of winning the lottery is 1/total
number of possible
combinations

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Hi Everyone,

I have a Worksheet with Combinations in the Format :-

01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
etc

The Combinations are on a Worksheet
Named "Combinations" and go from
A1:P65000 for example. Each Combination is in a
SINGLE CELL.

What I would like to be able to do is to COUNT how
many times the
following Combinations Appear in the Worksheet
Named "Combinations" :-

01-02-03
03-05-08
01-02-03-04
03-04-05-08
01-02-03-04-05
04-05-06-07-08
01-02-03-04-05-06
etc

There could be as many as 30 or so to Check, Ranging
from 3 Numbers up
to 6 Numbers.
The Combinations to Check will be in a Range on a
Worksheet Named
"Results" in A1:A30 for example.
Is there a Formula I could use to achieve this or
will it need to be
done using VB?.

Thanks very much in Advance.
Paul
.