Hi, I would like to format my spreadsheet so that it highlights duplicate entries that are consecutive, but only if they're more than a certain number, say 3. For instance, this is my data: F2: 1/1/21, F3: 1/1/21, F4: 1/3/21, F5: 1/1/21, F6: 1/1/21, F7: 1/1/21, F8: 1/1/21, F9: 1/5/21, F10: 1/5/21. In this case, I would like F5 through F8 to be highlighted (or I'd take just highlighting F8 if that's easier) because there are more than 3 dates that are the same consecutively. F2 and F3 or F9 and F10 would not be highlighted because there are only 2 that are consecutively the same. I've tried to combine rules for consecutive duplicates and counting more than a certain number and I've tried using those formulas as two stacked rules but I've gotten nothing to work right so far.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
highlight consecutive duplicates more than 3
- Thread starter LuluJ
- Start date
Toadstool
Well-known Member
- Joined
- Mar 5, 2018
- Messages
- 2,549
- Office Version
- 2016
- Platform
- Windows
Hi LuluJ,
I can highlight the 3rd and subsequent duplicates like this (NOTE: The format must start in row 4 because rows 2 and 3 cannot have two previous duplicates):
I can highlight the 3rd and subsequent duplicates like this (NOTE: The format must start in row 4 because rows 2 and 3 cannot have two previous duplicates):
| LuluJ.xlsx | |||
|---|---|---|---|
| F | |||
| 1 | Dates | ||
| 2 | 1/1/2021 | ||
| 3 | 1/1/2021 | ||
| 4 | 1/3/2021 | ||
| 5 | 1/1/2021 | ||
| 6 | 1/1/2021 | ||
| 7 | 1/1/2021 | ||
| 8 | 1/1/2021 | ||
| 9 | 1/5/2021 | ||
| 10 | 1/5/2021 | ||
| 11 | |||
| 12 | |||
| 13 | |||
| 14 | |||
| 15 | |||
| 16 | |||
| 17 | |||
Sheet1 | |||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F4:F16 | Expression | =AND(F4<>"",F2=F3,F3=F4) | text | NO |
Upvote
0
Thanks for the quick reply but I'm afraid things are a little more complicated. I listed 10 values to make my issue easy to understand but I actually have hundreds of lines of data with many repeated dates. I have copied a sample of some of my actual data:
| Book1.xlsx | |||
|---|---|---|---|
| A | |||
| 1 | Date Given | ||
| 2 | 5/1/2021 | ||
| 3 | 5/1/2021 | ||
| 4 | 5/1/2021 | ||
| 5 | 5/8/2021 | ||
| 6 | 5/8/2021 | ||
| 7 | 5/8/2021 | ||
| 8 | 5/9/2021 | ||
| 9 | 5/9/2021 | ||
| 10 | 5/9/2021 | ||
| 11 | 5/10/2021 | ||
| 12 | 5/10/2021 | ||
| 13 | 5/15/2021 | ||
| 14 | 5/15/2021 | ||
| 15 | 5/15/2021 | ||
| 16 | 5/15/2021 | ||
| 17 | 5/18/2021 | ||
| 18 | 5/18/2021 | ||
| 19 | 5/18/2021 | ||
| 20 | 5/19/2021 | ||
| 21 | 5/19/2021 | ||
| 22 | 5/19/2021 | ||
| 23 | 5/19/2021 | ||
| 24 | 5/21/2021 | ||
| 25 | 5/5/2021 | ||
| 26 | 5/5/2021 | ||
| 27 | 5/5/2021 | ||
| 28 | 5/6/2021 | ||
| 29 | 5/7/2021 | ||
| 30 | 5/12/2021 | ||
| 31 | 5/12/2021 | ||
| 32 | 5/12/2021 | ||
| 33 | 5/13/2021 | ||
| 34 | 5/13/2021 | ||
| 35 | 5/14/2021 | ||
| 36 | 5/17/2021 | ||
| 37 | 5/17/2021 | ||
| 38 | 5/17/2021 | ||
| 39 | 5/18/2021 | ||
| 40 | 5/18/2021 | ||
| 41 | 5/10/2021 | ||
| 42 | 5/10/2021 | ||
| 43 | 5/10/2021 | ||
| 44 | 5/10/2021 | ||
| 45 | 5/10/2021 | ||
| 46 | 5/10/2021 | ||
| 47 | 5/10/2021 | ||
| 48 | 5/10/2021 | ||
| 49 | 5/10/2021 | ||
| 50 | 5/13/2021 | ||
| 51 | 5/17/2021 | ||
| 52 | 5/17/2021 | ||
| 53 | 5/17/2021 | ||
| 54 | 5/17/2021 | ||
| 55 | 5/17/2021 | ||
| 56 | 5/3/2021 | ||
| 57 | 5/3/2021 | ||
| 58 | 5/6/2021 | ||
| 59 | 5/6/2021 | ||
| 60 | 5/6/2021 | ||
| 61 | 5/6/2021 | ||
| 62 | 5/6/2021 | ||
| 63 | 5/6/2021 | ||
| 64 | 5/6/2021 | ||
| 65 | 5/6/2021 | ||
| 66 | 5/6/2021 | ||
| 67 | 5/6/2021 | ||
| 68 | 5/10/2021 | ||
| 69 | 5/10/2021 | ||
| 70 | 5/10/2021 | ||
| 71 | 5/11/2021 | ||
| 72 | 5/11/2021 | ||
Sheet1 | |||
Upvote
0
Toadstool
Well-known Member
- Joined
- Mar 5, 2018
- Messages
- 2,549
- Office Version
- 2016
- Platform
- Windows
Yes, that's what it does. It highlights the first cell where the two previous rows are the same and also 4th, 5th, etc duplicates until a change.
| LuluJ.xlsx | |||
|---|---|---|---|
| A | |||
| 1 | Date Given | ||
| 2 | 5/1/2021 | ||
| 3 | 5/1/2021 | ||
| 4 | 5/1/2021 | ||
| 5 | 5/8/2021 | ||
| 6 | 5/8/2021 | ||
| 7 | 5/8/2021 | ||
| 8 | 5/9/2021 | ||
| 9 | 5/9/2021 | ||
| 10 | 5/9/2021 | ||
| 11 | 5/10/2021 | ||
| 12 | 5/10/2021 | ||
| 13 | 5/15/2021 | ||
| 14 | 5/15/2021 | ||
| 15 | 5/15/2021 | ||
| 16 | 5/15/2021 | ||
| 17 | 5/18/2021 | ||
| 18 | 5/18/2021 | ||
| 19 | 5/18/2021 | ||
| 20 | 5/19/2021 | ||
| 21 | 5/19/2021 | ||
| 22 | 5/19/2021 | ||
| 23 | 5/19/2021 | ||
| 24 | 5/21/2021 | ||
| 25 | 5/5/2021 | ||
| 26 | 5/5/2021 | ||
| 27 | 5/5/2021 | ||
| 28 | 5/6/2021 | ||
| 29 | 5/7/2021 | ||
| 30 | 5/12/2021 | ||
| 31 | 5/12/2021 | ||
| 32 | 5/12/2021 | ||
| 33 | 5/13/2021 | ||
| 34 | 5/13/2021 | ||
| 35 | 5/14/2021 | ||
| 36 | 5/17/2021 | ||
| 37 | 5/17/2021 | ||
| 38 | 5/17/2021 | ||
| 39 | 5/18/2021 | ||
| 40 | 5/18/2021 | ||
| 41 | 5/10/2021 | ||
| 42 | 5/10/2021 | ||
| 43 | 5/10/2021 | ||
| 44 | 5/10/2021 | ||
| 45 | 5/10/2021 | ||
| 46 | 5/10/2021 | ||
| 47 | 5/10/2021 | ||
| 48 | 5/10/2021 | ||
| 49 | 5/10/2021 | ||
| 50 | 5/13/2021 | ||
| 51 | 5/17/2021 | ||
| 52 | 5/17/2021 | ||
| 53 | 5/17/2021 | ||
| 54 | 5/17/2021 | ||
| 55 | 5/17/2021 | ||
| 56 | 5/3/2021 | ||
| 57 | 5/3/2021 | ||
| 58 | 5/6/2021 | ||
| 59 | 5/6/2021 | ||
| 60 | 5/6/2021 | ||
| 61 | 5/6/2021 | ||
| 62 | 5/6/2021 | ||
| 63 | 5/6/2021 | ||
| 64 | 5/6/2021 | ||
| 65 | 5/6/2021 | ||
| 66 | 5/6/2021 | ||
| 67 | 5/6/2021 | ||
| 68 | 5/10/2021 | ||
| 69 | 5/10/2021 | ||
| 70 | 5/10/2021 | ||
| 71 | 5/11/2021 | ||
| 72 | 5/11/2021 | ||
| 73 | |||
| 74 | |||
| 75 | |||
Sheet2 | |||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A4:A1910 | Expression | =AND(A4<>"",A2=A3,A3=A4) | text | NO |
Upvote
0
It's not working when I paste the formula into my sheet (I'm putting it into conditional formatting under a new rule). When I put that formula in, I get this (sample of some of my data):
| Employee Training History Report.xlsx | |||
|---|---|---|---|
| F | |||
| 1 | Date Given | ||
| 2 | 5/1/2021 | ||
| 3 | 5/1/2021 | ||
| 4 | 5/1/2021 | ||
| 5 | 5/8/2021 | ||
| 6 | 5/8/2021 | ||
| 7 | 5/8/2021 | ||
| 8 | 5/9/2021 | ||
| 9 | 5/9/2021 | ||
| 10 | 5/9/2021 | ||
| 11 | 5/10/2021 | ||
| 12 | 5/10/2021 | ||
| 13 | 5/15/2021 | ||
| 14 | 5/15/2021 | ||
| 15 | 5/15/2021 | ||
| 16 | 5/15/2021 | ||
| 17 | 5/18/2021 | ||
| 18 | 5/18/2021 | ||
| 19 | 5/18/2021 | ||
| 20 | 5/19/2021 | ||
| 21 | 5/19/2021 | ||
| 22 | 5/19/2021 | ||
| 23 | 5/19/2021 | ||
| 24 | 5/21/2021 | ||
| 25 | 5/21/2021 | ||
| 26 | 5/21/2021 | ||
| 27 | 5/21/2021 | ||
| 28 | 5/26/2021 | ||
| 29 | 5/26/2021 | ||
| 30 | 5/26/2021 | ||
| 31 | 5/24/2021 | ||
| 32 | 5/24/2021 | ||
| 33 | 5/24/2021 | ||
| 34 | 5/24/2021 | ||
| 35 | 5/24/2021 | ||
| 36 | 5/24/2021 | ||
| 37 | 5/24/2021 | ||
| 38 | 5/24/2021 | ||
| 39 | 5/24/2021 | ||
| 40 | 5/24/2021 | ||
| 41 | 5/27/2021 | ||
| 42 | 5/27/2021 | ||
| 43 | 5/27/2021 | ||
| 44 | 5/27/2021 | ||
| 45 | 5/27/2021 | ||
| 46 | 5/27/2021 | ||
| 47 | 5/27/2021 | ||
| 48 | 5/27/2021 | ||
| 49 | 5/27/2021 | ||
| 50 | 5/27/2021 | ||
Sheet1 | |||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F:F | Expression | =AND(F4<>"",F2=F3,F3=F4) | text | NO |
Upvote
0
Toadstool
Well-known Member
- Joined
- Mar 5, 2018
- Messages
- 2,549
- Office Version
- 2016
- Platform
- Windows
You selected the whole of column F for the formula but as I originally said "NOTE: The format must start in row 4 because rows 2 and 3 cannot have two previous duplicates" so apply to something like F4:F99999 and it should work.
Upvote
0
Solution
Thank you Toadstool. It looks like it's finally working.
Upvote
0
