Hi,

Here is what I have in A1 and A2 respectively:

4,5,6,7,8,9

4,5,6

I have this formula in A3 and A4:

=LEN(SUBSTITUTE(F15,",",""))

Which gives me 6 and 3 for answers.

In A5 I have (a3*a4), which = 18, or 18 combinations.

But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.

How would I construct a formula in such a way to achieve this?

Jim,

Don't enter mutliple values into one cell. Select A1:A2, and use Data / Text to columns, delimited,
check 'comma' and click OK to spread your values out into individual cells.

Then in A3, enter the formula

=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)

and copy to B3, C3, etc, for as many cells as you have in row 2.

Then use the formula

=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)

to give your combinations.

HTH,
Bernie
MS Excel MVP


"JimS" <jimx22@msn.com> wrote in message news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
> Hi,
>
> Here is what I have in A1 and A2 respectively:
>
> 4,5,6,7,8,9
>
> 4,5,6
>
> I have this formula in A3 and A4:
>
> =LEN(SUBSTITUTE(F15,",",""))
>
> Which gives me 6 and 3 for answers.
>
> In A5 I have (a3*a4), which = 18, or 18 combinations.
>
> But I don't want to count the matching numbers. In this case that
> would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
>
> How would I construct a formula in such a way to achieve this?
>
>

Thanks. I purposely put multiple data into one cell. I didn't copy
this data from another source. It's much easier to do it this way,
and will be easier for the people using the spread sheet. Considering
that, is there still a way to do it?

I have this data in h15 and i15:

2,3,4
3,4,5

I have the following formula in J15, which counts the combinations but
includes the matches. I just need to exclude the matches and I'm set.

=IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUB STITUTE(I15,",",""))))

With this formula I get 9. I need to fix it so I get 7.

Thanks for your formula. I can use that elsewhere.


On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:

>Jim,
>
>Don't enter mutliple values into one cell. Select A1:A2, and use Data / Text to columns, delimited,
>check 'comma' and click OK to spread your values out into individual cells.
>
>Then in A3, enter the formula
>
>=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
>
>and copy to B3, C3, etc, for as many cells as you have in row 2.
>
>Then use the formula
>
>=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
>
>to give your combinations.
>
>HTH,
>Bernie
>MS Excel MVP
>
>
>"JimS" <jimx22@msn.com> wrote in message news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
>> Hi,
>>
>> Here is what I have in A1 and A2 respectively:
>>
>> 4,5,6,7,8,9
>>
>> 4,5,6
>>
>> I have this formula in A3 and A4:
>>
>> =LEN(SUBSTITUTE(F15,",",""))
>>
>> Which gives me 6 and 3 for answers.
>>
>> In A5 I have (a3*a4), which = 18, or 18 combinations.
>>
>> But I don't want to count the matching numbers. In this case that
>> would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
>>
>> How would I construct a formula in such a way to achieve this?
>>
>>
>

Jim,

You could use formulas to parse out the strings into separate cells for
processing, or you could use a UDF, if you are not averse to having macros
in your workbooks. Also, note that your formula doesn't work for numbers
greater than 9 - you could count the commas and add one...

HTH,
Bernie
MS Excel MVP


"JimS" <jimx22@msn.com> wrote in message
news:ob0eb4d464cdkpriinnu406fh47mgr9q5j@4ax.com...
> Thanks. I purposely put multiple data into one cell. I didn't copy
> this data from another source. It's much easier to do it this way,
> and will be easier for the people using the spread sheet. Considering
> that, is there still a way to do it?
>
> I have this data in h15 and i15:
>
> 2,3,4
> 3,4,5
>
> I have the following formula in J15, which counts the combinations but
> includes the matches. I just need to exclude the matches and I'm set.
>
> =IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUB STITUTE(I15,",",""))))
>
> With this formula I get 9. I need to fix it so I get 7.
>
> Thanks for your formula. I can use that elsewhere.
>
>
> On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
> consumer dot org> wrote:
>
>>Jim,
>>
>>Don't enter mutliple values into one cell. Select A1:A2, and use Data /
>>Text to columns, delimited,
>>check 'comma' and click OK to spread your values out into individual
>>cells.
>>
>>Then in A3, enter the formula
>>
>>=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
>>
>>and copy to B3, C3, etc, for as many cells as you have in row 2.
>>
>>Then use the formula
>>
>>=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
>>
>>to give your combinations.
>>
>>HTH,
>>Bernie
>>MS Excel MVP
>>
>>
>>"JimS" <jimx22@msn.com> wrote in message
>>news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com. ..
>>> Hi,
>>>
>>> Here is what I have in A1 and A2 respectively:
>>>
>>> 4,5,6,7,8,9
>>>
>>> 4,5,6
>>>
>>> I have this formula in A3 and A4:
>>>
>>> =LEN(SUBSTITUTE(F15,",",""))
>>>
>>> Which gives me 6 and 3 for answers.
>>>
>>> In A5 I have (a3*a4), which = 18, or 18 combinations.
>>>
>>> But I don't want to count the matching numbers. In this case that
>>> would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
>>>
>>> How would I construct a formula in such a way to achieve this?
>>>
>>>
>>
>

On Thu, 28 Aug 2008 16:41:55 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:

>Jim,
>
>You could use formulas to parse out the strings into separate cells for
>processing,

This sounds like a plan.

>or you could use a UDF, if you are not averse to having macros

I'm not averse to macros, but I would rather avoid them if I can.

>in your workbooks. Also, note that your formula doesn't work for numbers
>greater than 9 - you could count the commas and add one...

Since I will rarely use a number larger than twelve, I can use 0 for
ten, x for 11 and y for 12. Or something similar.

Now to figure out those formulas.


>
>HTH,
>Bernie
>MS Excel MVP
>
>
>"JimS" <jimx22@msn.com> wrote in message
>news:ob0eb4d464cdkpriinnu406fh47mgr9q5j@4ax.com.. .
>> Thanks. I purposely put multiple data into one cell. I didn't copy
>> this data from another source. It's much easier to do it this way,
>> and will be easier for the people using the spread sheet. Considering
>> that, is there still a way to do it?
>>
>> I have this data in h15 and i15:
>>
>> 2,3,4
>> 3,4,5
>>
>> I have the following formula in J15, which counts the combinations but
>> includes the matches. I just need to exclude the matches and I'm set.
>>
>> =IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUB STITUTE(I15,",",""))))
>>
>> With this formula I get 9. I need to fix it so I get 7.
>>
>> Thanks for your formula. I can use that elsewhere.
>>
>>
>> On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
>> consumer dot org> wrote:
>>
>>>Jim,
>>>
>>>Don't enter mutliple values into one cell. Select A1:A2, and use Data /
>>>Text to columns, delimited,
>>>check 'comma' and click OK to spread your values out into individual
>>>cells.
>>>
>>>Then in A3, enter the formula
>>>
>>>=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
>>>
>>>and copy to B3, C3, etc, for as many cells as you have in row 2.
>>>
>>>Then use the formula
>>>
>>>=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
>>>
>>>to give your combinations.
>>>
>>>HTH,
>>>Bernie
>>>MS Excel MVP
>>>
>>>
>>>"JimS" <jimx22@msn.com> wrote in message
>>>news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com ...
>>>> Hi,
>>>>
>>>> Here is what I have in A1 and A2 respectively:
>>>>
>>>> 4,5,6,7,8,9
>>>>
>>>> 4,5,6
>>>>
>>>> I have this formula in A3 and A4:
>>>>
>>>> =LEN(SUBSTITUTE(F15,",",""))
>>>>
>>>> Which gives me 6 and 3 for answers.
>>>>
>>>> In A5 I have (a3*a4), which = 18, or 18 combinations.
>>>>
>>>> But I don't want to count the matching numbers. In this case that
>>>> would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
>>>>
>>>> How would I construct a formula in such a way to achieve this?
>>>>
>>>>
>>>
>>
>

Let me ask you this. Is there an easy way to count the number of
matches in the following two cells?

127

12457

The answer would be 3.

If I can do that I've got a solution to my problem.


On Thu, 28 Aug 2008 16:41:55 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:

>Jim,
>
>You could use formulas to parse out the strings into separate cells for
>processing, or you could use a UDF, if you are not averse to having macros
>in your workbooks. Also, note that your formula doesn't work for numbers
>greater than 9 - you could count the commas and add one...
>
>HTH,
>Bernie
>MS Excel MVP
>
>
>"JimS" <jimx22@msn.com> wrote in message
>news:ob0eb4d464cdkpriinnu406fh47mgr9q5j@4ax.com.. .
>> Thanks. I purposely put multiple data into one cell. I didn't copy
>> this data from another source. It's much easier to do it this way,
>> and will be easier for the people using the spread sheet. Considering
>> that, is there still a way to do it?
>>
>> I have this data in h15 and i15:
>>
>> 2,3,4
>> 3,4,5
>>
>> I have the following formula in J15, which counts the combinations but
>> includes the matches. I just need to exclude the matches and I'm set.
>>
>> =IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUB STITUTE(I15,",",""))))
>>
>> With this formula I get 9. I need to fix it so I get 7.
>>
>> Thanks for your formula. I can use that elsewhere.
>>
>>
>> On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
>> consumer dot org> wrote:
>>
>>>Jim,
>>>
>>>Don't enter mutliple values into one cell. Select A1:A2, and use Data /
>>>Text to columns, delimited,
>>>check 'comma' and click OK to spread your values out into individual
>>>cells.
>>>
>>>Then in A3, enter the formula
>>>
>>>=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
>>>
>>>and copy to B3, C3, etc, for as many cells as you have in row 2.
>>>
>>>Then use the formula
>>>
>>>=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
>>>
>>>to give your combinations.
>>>
>>>HTH,
>>>Bernie
>>>MS Excel MVP
>>>
>>>
>>>"JimS" <jimx22@msn.com> wrote in message
>>>news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com ...
>>>> Hi,
>>>>
>>>> Here is what I have in A1 and A2 respectively:
>>>>
>>>> 4,5,6,7,8,9
>>>>
>>>> 4,5,6
>>>>
>>>> I have this formula in A3 and A4:
>>>>
>>>> =LEN(SUBSTITUTE(F15,",",""))
>>>>
>>>> Which gives me 6 and 3 for answers.
>>>>
>>>> In A5 I have (a3*a4), which = 18, or 18 combinations.
>>>>
>>>> But I don't want to count the matching numbers. In this case that
>>>> would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
>>>>
>>>> How would I construct a formula in such a way to achieve this?
>>>>
>>>>
>>>
>>
>

Hello Jim,

with your numbers in A1 and A2, you can use this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1 ))),
1),A2))))

It will give you the number of matches, digit by digit.

Hope this helps.

Pete

On Aug 29, 11:07*am, JimS <jim...@msn.com> wrote:
> Let me ask you this. *Is there an easy way to count the number of
> matches in the following two cells?
>
> 127
>
> 12457
>
> The answer would be 3.
>
> If I can do that I've got a solution to my problem.
>

This works perfectly. Even if I undersood how to use all of the
separate functions in this formula...isnumber, search, mid, row,
indirect and len. I don't think I could have properly constructed a
similar formula if I had the rest of my lifetime.

I am just amazed how you guys know all this.

Thanks again, very much appreciated.

On Fri, 29 Aug 2008 04:53:31 -0700 (PDT), Pete_UK
<pashurst@auditel.net> wrote:

>Hello Jim,
>
>with your numbers in A1 and A2, you can use this:
>
>=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1 ))),
>1),A2))))
>
>It will give you the number of matches, digit by digit.
>
>Hope this helps.
>
>Pete
>
>On Aug 29, 11:07*am, JimS <jim...@msn.com> wrote:
>> Let me ask you this. *Is there an easy way to count the number of
>> matches in the following two cells?
>>
>> 127
>>
>> 12457
>>
>> The answer would be 3.
>>
>> If I can do that I've got a solution to my problem.
>>

You're welcome, Jim - thanks for feeding back.

Pete

On Aug 29, 6:31*pm, JimS <jim...@msn.com> wrote:
> This works perfectly. *Even if I undersood how to use all of the
> separate functions in this formula...isnumber, search, mid, row,
> indirect and len. *I don't think I could have properly constructed a
> similar formula if I had the rest of my lifetime.
>
> I am just amazed how you guys know all this.
>
> Thanks again, very much appreciated.
>
> On Fri, 29 Aug 2008 04:53:31 -0700 (PDT), Pete_UK
>
>
>
> <pashu...@auditel.net> wrote:
> >Hello Jim,
>
> >with your numbers in A1 and A2, you can use this:
>
> >=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1 ))),
> >1),A2))))
>
> >It will give you the number of matches, digit by digit.
>
> >Hope this helps.
>
> >Pete
>
> >On Aug 29, 11:07*am, JimS <jim...@msn.com> wrote:
> >> Let me ask you this. *Is there an easy way to count the number of
> >> matches in the following two cells?
>
> >> 127
>
> >> 12457
>
> >> The answer would be 3.
>
> >> If I can do that I've got a solution to my problem.- Hide quoted text -
>
> - Show quoted text -