how many cell in a row that contain certain combination from a larger combination

hi all

does any one know how to count how many cell in a row that contain certain combination from a larger combination

example: i have this combination: 1 2 3 4 5 6

in hundreds or thousands of cells in a row i have combinations such

1 2 3 4 5 6 7
1 2 3 4 5 6 8
1 2 3 4 5 6 9
1 2 3 4 5 6 10
1 2 3 4 5 7 8
1 2 3 4 5 7 9
1 2 3 4 5 7 10 etc

i we know here that i have 4 cells that contain 1 2 3 4 5 6, but the problem is have thousands of cells to check, and not in ascending order like in the example where i can sort them

and another example: i have this combination 1 4 7 9

and in hundreds or thousands of cells in a row i have combinations such:

1 2 5 8 12 13
1 4 7 9 10 12
1 2 3 4 7 9
1 3 4 6 7 9
1 4 7 8 9 10
in this example i have 4 cells that contain 1 4 7 9

kind regards

I Simulated your problem in a file. Hope that is what you want. Answers are in yellow cells

hi eisayew, thank you for your time but thats now i count them, not how many each number in combination appear, but how many cell that contain whole combination in this case "1 2 3 4 5 6" appear 4 times

and to alansidman, it is in one row, yes 1 combination 1 cell, so 1 2 3 4 5 6 is in one cell

Hi.
I came up with that solutions. I use helper columns to identify wether cell includes required set of numbers which all of them in one cell. then sum those 1"s to get total number

=IF(COUNT(FIND(ISNUMBER(MID($E$1,ROW(INDIRECT("1:"&LEN($E$1))),1)*1)*MID($E$1,ROW(INDIRECT("1:"&LEN($E$1))),1),A1,1))=COUNT(MID($E$1,ROW(INDIRECT("1:"&LEN($E$1))),1)*1),1,0)

hi eisayew, that is actually very good , i am amaze, will try it in my work sheet and let you know soon

kind regards

hi eisayew, can you please tell me how to apply your formula in a step by step format, thank you

hi alansidman, can you download the file that eisayew upload?

thanks guys

Originally Posted by system

hi eisayew, can you please tell me how to apply your formula in a step by step format, thank you

thanks guys

Hi System,
Column A includes base of combinations. Cell E1 is the numbers which you want to compare.
Column B is the helper column. The result of this formula will give you 1 or 0. To know how many cells include the desired outcome you should sum those 1s and 0s.
To talk about the formula:
Formula compares each number in a cell with the set of numbers in base and counts how many of these numbers are in base numbers. If they are equal then 1 otherwise o.
I dont know wether the formula fits to your real numbers or not. You should check I think.

hi eisayew, in which column should i enter the formula? and how to drag it? or apply it?

thank you

hi eisayew, in which column should i enter the formula? and how to drag it? or apply it?

thank you

Originally Posted by system

hi eisayew, in which column should i enter the formula? and how to drag it? or apply it?

thank you

You should enter the formula to column B. Suppose that your numbers in A1:A10, then formula should be entered to B1:B10

Originally Posted by system

hi eisayew, and if my my numbers are from a1 to a10000? you drag it down from b1:b10000? or need to edit the formula to b1:b10? where? sorry just wanna be sure
thanks

Yes. You should drag it down till 10000. (Or just double click from the corner of active cell).

hi eisayew, it doesn't work in my workksheet, all column b says 1 all the way down, here i attach one of many sets of
combinations i need to check, the amount of number in cell vary, although each set has same amount of numbers in the cell,
eg. this one 18 numbers, other can be 7 numbers in cell or 8,9,10-30

kind regards

Hi. actually I found a solution for your problem and its formula is too long. Bu ACE_XL has already figured it out. But I want to present you my formula. Feel a little uncomfortable to come with such a long formula
=IF(IF(SUM(IF(ISNUMBER(MATCH(TRIM(MID(LEFT($E$1,FIND(" ",$E$1,1))&$E$1,FIND(" ",LEFT($E$1,FIND(" ",$E$1,1))&$E$1,ROW(INDIRECT("1:"&LEN(LEFT($E$1,FIND(" ",$E$1,1))&A1)))),3))+0,TRIM(MID(LEFT(A1,FIND(" ",A1,1))&A1,FIND(" ",LEFT(A1,FIND(" ",A1,1))&A1,ROW(INDIRECT("1:"&LEN(LEFT(A1,FIND(" ",A1,1))&A1)))),3))+0,0)),1,0))=0,0,SUM(IF(ISNUMBER(MATCH(TRIM(MID(LEFT($E$1,FIND(" ",$E$1,1))&$E$1,FIND(" ",LEFT($E$1,FIND(" ",$E$1,1))&$E$1,ROW(INDIRECT("1:"&LEN(LEFT($E$1,FIND(" ",$E$1,1))&A1)))),3))+0,TRIM(MID(LEFT(A1,FIND(" ",A1,1))&A1,FIND(" ",LEFT(A1,FIND(" ",A1,1))&A1,ROW(INDIRECT("1:"&LEN(LEFT(A1,FIND(" ",A1,1))&A1)))),3))+0,0)),1,0)))=IF(SUM(IF(ISNUMBER(TRIM(MID(LEFT($E$1,FIND(" ",$E$1,1))&$E$1,FIND(" ",LEFT($E$1,FIND(" ",$E$1,1))&$E$1,ROW(INDIRECT("1:"&LEN(LEFT($E$1,FIND(" ",$E$1,1))&A1)))),3))+0),1,0))=0,99,SUM(IF(ISNUMBER(TRIM(MID(LEFT($E$1,FIND(" ",$E$1,1))&$E$1,FIND(" ",LEFT($E$1,FIND(" ",$E$1,1))&$E$1,ROW(INDIRECT("1:"&LEN(LEFT($E$1,FIND(" ",$E$1,1))&A1)))),3))+0),1,0))),1,0)

hi eisayew, same procedure with your new formula? just put it in row b and drag all the way?

cheers

Originally Posted by system

hi eisayew, same procedure with your new formula? just put it in row b and drag all the way?

cheers

Yes. same procedure but different formula. Just check the uploaded file. You will see there