I need a simple code to resolve the following

[rufuxinix]

Good morning

This should be easily done if not in Excel tehn in VBA

I need a simple code to resolve the following

I have a table with cells filled with numbers, or words, and cells which are empty, randomly.

I want to automatically depict next to each row the following example. row as 121, where the contents of that row would be

Example:

The contents of the row would be as below and the result would show, on a cell net to the row, 121

Filled cell / Empty cell /Filled Cell / Filled cell / empty cell / Filled cell = 121

Note: empty cells can be consecutively more than one and the result would still be 121, such as below

Filled cell / Empty cell /Filled Cell / Filled cell / empty cell / empty cell / Filled cell = 121

Some other examples

Filled cell / Filled Cell / Filled cell / empty cell / empty cell / Filled cell = 31
Filled cell /Filled Cell / Filled cell / empty cell / empty cell / empty cell = 3
empty cell / empty cell / Filled cell /Filled Cell / Filled cell = 3


Hope someone can help.

Thank you for your attention

Regards

 

[jasonb75]

I don't think it will be possible with formulas without a helper column

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1										Original formula	Helper	Result
2				4		6		8		111	468	3
3		2				6		8		111	268	3
4	1		3	4	5	6	7	8		16	1345678	1
5					5		7	8		12	578	2
6			3		5		7			111	357	3
7			3		5		7			111	357	3
8	1	2					7	8		22	1278	3
9		2			5	6				12	256	2
10			3	4		6	7			22	3467	3
11					5					1	5	1
12	1	2				6	7			22	1267	3
13	1			4	5	6				13	1456	1
Sheet6

Cell Formulas
Range	Formula
J2:J13	J2	=CONCAT(IFERROR(1/(1/FREQUENCY(IF($A2:$H2<>"",COLUMN($A2:$H2)),IF($A2:$H2="",COLUMN($A$2:$H$2)))),""))
K2:K13	K2	=VALUE(CONCAT(A2:H2))
L2:L13	L2	=SUM(IF(FREQUENCY(IF($J$2:$J$13=$J2,$K$2:$K$13),$K$2:$K$13)>0,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rufuxinix]

Thanks Jason, but i'm not getting it.
You say it might not be possible to calculate, without a helper column.
With the helper column the Result column is not showing the correct answer.
Am i missing something?
Thanks for trying.

 

[jasonb75]

I thought that you wanted the count of different 111 combinations in the list, in the table there are 4 rows with 111, but only 3 are different (unique) the last one is a duplicate.
Is that not correct?

 

[rufuxinix]

No, what I want to know is, in a roster as above, how many variations can i have that will meet the condition for 111, or 121, or whatever.
So with 8 fields, for 111 on my example above, there will be 16 variations: 135, 136, 137, 138, 146, 147, 148, 157, 158, 246, 247, 248, 257, 258, 357, 358.
For, all the other possibilities, like 212, 12, 22, whatever it is, there would be many other variations.
To work all this out by hand is too laborious.
Thanks for the interest and help!

 

[jasonb75]

For, all the other possibilities, like 212, 12, 22, whatever it is, there would be many other variations.

I see what you mean now, I'll give it some thought but I'm not sure that what you want will be possible without a reference list of the known combinations.