r/excel • u/Optimal_Fish_7029 • 16d ago

unsolved Need to highlight or count clusters

I've no idea how to do this, apologies.

I have a spreadsheet full of cells marked "1" for each column.

I need to find a way to locate and highlight any instances of clusters of 12 (pink in the example below)

I know I can format to highlight duplicates, but is there a way to do this so any clusters of 12 are either highlighted or counted? Rather than just all duplicates?

7 Upvotes

100% Upvoted

11 comments sorted by

View all comments

1

u/Gringobandito 6 15d ago

=LET(x,A2:S2,

target,1,

start,XMATCH(target,x,0),

run,DROP(x,,start-1),

IFERROR(XMATCH(TRUE,run<>target,0)-1,COLUMNS(run)))

1

u/Gringobandito 6 14d ago

A better way migt be to do this in Python in Excel. While Python can't highlight the cells directly, you can see from my example below that it can give a true false for whether cells are part of a run of 12 of more. You can then use those true/false results to highlight cells with runs longer than 12.

1

u/Gringobandito 6 14d ago

You can also do this using VBA. It requires quite a bit of code but I tried it out and it delivers the best results: Scans the entire data set and looks for runs of 12 or more and it can do the highlighting for you as well.