 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2008Counting Combinations Question
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 > Hi, > [quoted text clipped - 16 lines] > > 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(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. >Jim, > [quoted text clipped - 37 lines] >> >> 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 > 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, [quoted text clipped - 58 lines] >>> >>> 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, 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 [quoted text clipped - 62 lines] >>>> >>>> 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. >Jim, > [quoted text clipped - 69 lines] >>>> >>>> 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 > Let me ask you this. Is there an easy way to count the number of > matches in the following two cells? [quoted text clipped - 6 lines] > > 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. >Hello Jim, > [quoted text clipped - 19 lines] >> >> If I can do that I've got a solution to my problem. You're welcome, Jim - thanks for feeding back. Pete > This works perfectly. Even if I undersood how to use all of the > separate functions in this formula...isnumber, search, mid, row, [quoted text clipped - 33 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.