Count number of contiguous equal cells

[Belthazar]

I have an array of letters which are arranged in some abitrary shapes - for example, say:

OXOXOOXO
OXOXOOXO
OXXXOOXO
OXOXOOXO
OXOXOOXO

I want to create a second array such that each cell in the array is equal to the number of cells in the region of the corresponding cell, or in other words, the area of each region - that is, each cell needs to count how many cells in its neighbourhood share its value. For this example above, that'd be, uh...

5b2baa55
5b2baa55
5bbbaa55
5b2baa55
5b2baa55

(Where I've used a and b here to mean the hexadecimal 10 and 11 for the purposes of keeping this properly table aligned - the real one can use whatever size number it likes.) Since there are non-contiguous areas using the same letters, then naturally a plain ol' COUNTIF won't do the trick. I'd like to do this in pure Excel, no VBA. I feel like this an algorithm that ought to exist, but all of my ideas so far either leave cells out or keep double-counting cells.

 

[saboh12617]

Hello,

It is a very interesting but complicated problem i think. Please find attached my WIP attempt. I managed to find a reliable way to count the adjacent cells in the array, but i then need to spray this value to its neighbours, and i did it by repeating the formula manually, however when i put it inside a REDUCE/VSTACK it wont work for some reason. I will continue tomorrow.

I attach a file because the formulas are sprayed for the moment.

Download Data package from December 12th.

 

[Peter_SSs]

@saboh12617
Please review #4 of the Forum Rules, particularly the last paragraph regarding providing solutions or suggestions by linked file only. Note that providing such a file link as an 'extra' is fine, but your suggestion should be provided in the thread itself for the reasons outlined in the rule.

 

[saboh12617]

Hello @Peter_SSs,

Apologizes for that, I had forgotten. Also i realized this morning that my scan of the array was not handling all shapes so the linked file is of no interest and can be removed.

Have a good day.

 

[Belthazar]

Yeah, arbitrary shapes is hard. I can think of a few algorithms that would probably work for several different sorts of shape, but nothing that'll work for any possibility. And a shape like, say, this one below breaks everything I can come up with. Lobes in all possible directions.

...........
.....X.....
....XXX....
.....X....
..X..X..X..
.XXXXXXXXX.
..X..X..X..
.....X.....
....XXX....
.....X.....
...........