Identifying a Range & altering formatting

[Battychick]

Hi.

I'm stuck with an exceptionally large file that needs "prettying up" before sending for work, and I'm spending hours doing the following manually.

Lets say I have a column B, and each row has a weekend identifier:
B1 Weekend 1
B2 Weekend 2
B3 Weekend 3
B4 Weekend 5
B5 Weekend 9
B6 Weekend 10
B7 Weekend 11
B8 Weekend 3
B9 Weekend 4

So I need to identify the 3 consecutive weekends, and format it to red bold as shown. The previous column has an identifier (so B1 - B7 is one group, and B8 onwards another)

BTW, this is just one fo the tabs on the sheet. The other tabs contain various other data sets. So I need something that can be run over specific tabs only.

Obviously, the sheet I'm on has 30,000 rows (and that's just one of the 84 reports)

If anyone can help me do this an easier way, I'd be most grateful.

 

[Gerald Higgins]

Hi, welcome to the board.

Conditional formating will do this for you, at least for the examples you provided.

For example, apply this to cell B6
Formula Is

=OR(AND(RIGHT(B6,2)+1=RIGHT(B7,2)+0,RIGHT(B7,2)+1=RIGHT(B8,2)+0),
AND(RIGHT(B6,2)+1=RIGHT(B7,2)+0,RIGHT(B6,2)-1=RIGHT(B5,2)+0),
AND(RIGHT(B6,2)-1=RIGHT(B5,2)+0,RIGHT(B5,2)-1=RIGHT(B4,2)+0))

and format as required.

This won't work on the first couple of rows (because it is looking for rows that are off the top of the spreadsheet) but it should work for the rest of the sheet.
Note, it will probably also highlight any chains of MORE THAN 3 weekends, if they exist.
It will run into problems if week numbers exceed 99.

There may be better ways of doing this.

 

[Battychick]

Thanks. It doesn't quite work. It only pickes up some of the consecutive weekends, and not all.

Below is the result on a range:
<table border="0" cellpadding="0" cellspacing="0" width="250"><col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt;width:93pt" height="17" width="124">Weekend02</td> <td class="xl124" style="width:95pt" align="center" width="126">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend03</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend04</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend05</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend09</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend11</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend12</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend13</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend01</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend02</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend04</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend05</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend05</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend06</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend08</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend09</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend10</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend11</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend11</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend12</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend13</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend01</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend02</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend02</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend03</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend03</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend04</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend04</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend05</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend05</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend06</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend07</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend07</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend08</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend08</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend09</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend10</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend10</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend11</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend12</td> <td class="xl124" align="center">TRUE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend12</td> <td class="xl124" align="center">FALSE</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl125" style="height:12.75pt" height="17">Weekend13</td> <td class="xl124" align="center">FALSE</td> </tr> </tbody></table>

 

[Gerald Higgins]

So which are the errors on your list in post #3 ?

 

[Battychick]

ah, I just realized, the formula doesn't allow for one cell to have the same value as the next / previous.
I'll try and build an additional level into the formula to allow for same values (though I suspect I'm going to make a dogs dinner of it)

 

[Gerald Higgins]

I think that's going to be difficult to do.

For example, if this SHOULD trigger highlighting
Weekend 4
Weekend 5
Weekend 5
Weekend 5
Weekend 6

and this should NOT
Weekend 3
Weekend 5
Weekend 5
Weekend 5
Weekend 6

 

[Battychick]

Rats. Guess I'll have to kiss the next 3 days goodbye!

 

[Gerald Higgins]

Don't give up yet, it might be possible

But can you give us examples of ALL the scenarios you want to identify ?

 

[Battychick]

Okay. So the same still applies, I need to identify those entries that give me a minimum of 3 consecutive weekends.

The weekend entries are based on date so:
June 11 = Weekend 2
June 12 = Weekend 2
June 18 = Weekend 3

The maximum a weekend appears in a group is twice. (with the potential of the entire group duplicating i.e. w1, w1, w2, w2, w3, w3 etc) The maximum number of weekends is 14, as I'm looking over a 3 month period.

This means the original formula would need to be able to indentify the minimum 3 consecutive weekends, whilst allowing for 1 duplicate entry per weekend allocation.

Perhaps we can use the actual dates, and subtract dates rather than pick the weekend allocation?

 

[Gerald Higgins]

If you're willing to use helper columns, here's a solution.
It's not very elegant, but it seems to work for the samples of data you have provided.

Assuming your weekends are in Col A, starting on row 2.

In B2

=OR((RIGHT(A2,2)+0)=(RIGHT(A1,2)+1),(RIGHT(A2,2)+0)=(RIGHT(A1,2)+0),(RIGHT(A2,2)+0)=(RIGHT(A3,2)-1),(RIGHT(A2,2)+0)=(RIGHT(A3,2)+0))

In C2

=IF(B2,IF(AND(B2=B1,A2=A1),C1,C1+1))

In D2

=IF(C2=3,3,IF(C2=FALSE,0,IF(D3=3,3,0)))

Copy these down as far as required.

This will put a 3 on any entry that is part of a group of 3, and 0 on those that aren't.
It probably won't do it quite right for the very first and last entries, which you'll need to do manually.

You can then either filter out all the 0s, or do some conditional formating that refers to the 3s and 0s.