Count contiguous cells with same value?

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
70
Say I have a 21x21 range in cells A1:U21.
The content of the cells will change regularly, but at certain intervals I want to know some information about the contents.
Values in the cells will only ever be a single letter, or empty.

Is there any way to calculate the longest path of orthogonally adjacent cells with the same value?
Or even better, the longest path of orthogonally adjacent cells with one of three values?
There may be several different (and branching) paths of the same value, but I only care to know the length of the longest one.

Here is a visual example:

Path Example.png

In this image, I would want to know the length of the longest path made of the letters R, B and D which I have manually identified with red text across the middle.
I would also want to know the length of the longest path made of the letters W, B and D which I have manually identified highlighted in yellow.

It may be possible, as in this case, that there are two identically long paths. As I don't care for the location of the path, either is a valid result as the length is identical for both.

Does anyone have any ideas how I might go about automating identifying longest contiguous paths within a range?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Update:

I've made some progress with this, but I think there must be a simpler way than what I'm currently attempting.

I have made a new sheet for the calculations.
From the original grid, using
Code:
=IF(OR(Sheet1!A1="R",Sheet1!A1 = "B",Sheet1!="D"),1,"X")
I have managed to produce a grid of 1s where the path exists, and Xs where it does not.

Below this new grid I have a second grid looking at whether a cell and the cell to its immediate left are both numbers, and adding 1 to the number each time.
Code:
=IF(AND(ISNUMBER(A25),ISNUMBER(B2)),A25+1,B2)
This produces a running total of all paths going left to right.

So far so good, but each path can have multiple changes of direction, so to build on this method would require a whole series of grids anticipating the potential changes of direction of any given path. This would be exhaustive and inelegant.

Can anyone guide me towards a more elegant solution where instead of looking in only a single direction each time, I am able to produce a formula that looks in all 4 orthogonally adjacent cells and add 1 in each direction where relevant? Even if this means having four different grids, starting from the left, top, right and bottom, it would be significantly more efficient than the iterative method I'm currently stuck with.

Once I have one (or more) grid(s) with the cumulative totals for each path mapped out, I'm planning on using MAX to return the value of the longest path which will give me the end result that I am looking for.
 
Upvote 0
there is a tree function in excel, so you could make at each crossing a node and 2 new paths, a kind of parent-child system.
As long as they don't come together a 2nd time, you could sum the max path, but that includes VBA.
Trees aren't my specialties,
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top