Counting Combinations Question

[JimS]

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?

 

[Bernie Deitrick]

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]

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(SUBSTITUTE(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.

 

[Bernie Deitrick]

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]

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.

 

[JimS]

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.

 

[Pete_UK]

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

 

[JimS]

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.

 

[Pete_UK]

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

Pete