Highlight cells that have matching sequences of letters?

[acmarston]

I have tens of thousands of rows in one column with either the letter A or the letter B randomly. They're all separated into groups of 6 rows with a blank cell in between them. What I'm trying to do is, I want to highlight wherever there are 3 consecutive groups of 6 with exactly the same sequence of A or B as each other, if that makes sense.

Can this be done?

[FDibbins]

Can you provide a small sample WB showing what you are working with?

[acmarston]

Have attached an example.

Rows 22 to 41 in the A column would be what I would like highlighted.

[acmarston]

Any ideas?

[AliGW]

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post and then no more than once a day - it has been only a couple of hours since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[Marius44]

Hello
One solution could be this code

Please Login or Register  to view this content.

Let them know.
Hello,
Mario

[Marius44]

Hello
I misunderstood. I think this code is more suitable for your needs.

Please Login or Register  to view this content.

Hello,
Mario

[Bo_Ry]

Please try CF formula applies to A1:A60

=(SUBSTITUTE(CONCAT(OFFSET(A$1,FLOOR(ROWS(A$1:A1),21),,20)),CONCAT(OFFSET(A$1,FLOOR(ROWS(A$1:A1),21),,6)),)="")*(A1<>"")

[FDibbins]

Please try CF formula applies to A1:A60

=(SUBSTITUTE(CONCAT(OFFSET(A$1,FLOOR(ROWS(A$1:A1),21),,20)),CONCAT(OFFSET(A$1,FLOOR(ROWS(A$1:A1),21),,6)),)="")*(A1<>"")

Im sure there are many of us (myself included) that would love to know exactly what that formula does - any chance of an explanation?

[Bo_Ry]

Ok, I'll try.

First, I can use volatile function as CF is already volatile.

a. FLOOR(ROWS(A$1:A1),21) give 0, 21 , 42 on every 21 row

b. CONCAT(OFFSET(A$1,FLOOR(a,,6)) to get pattern of 6 row,

c. CONCAT(OFFSET(A$1,FLOOR(a,,20)) to get pattern of 6 row x3 ,

d. SUBSTITUTE(c(6x3),b(6),) substitute pattern of 6 row x3 with pattern of 6 row with null string

e. d="" if result is null string. this mean pattern of 6 row is the same for pattern of 6 row x3


** Then I notice that I only check every 0, 21 , 42 I need to add every 7, 28 , 49 and 14, 35, 56

and also need to check if the 6x3 has data, COUNTA(OFFSET(A$1,FLOOR(ROWS(A$1:A1),21),,20)>17

so new long and painful CF formula

Please Login or Register  to view this content.