Warning for Consecutive repeated entries

**LewisK** · 04-01-2014, 05:12 AM

Hi All, new member with a problem please!

I have a spreadsheet which is basically a combined machine log - column A is the name of the machine, columns B-Y contain information about the setup of the machine, and then column Z is a numeric 'result'.

What I would like to acheive is to have some kind of warning to popup if there are 3 consecutive entries of the same machine name and a result of "0".

Capture.JPG

So there would be a warning message now because a user had entered "SL3" as the machine and "0" as the result 3 times consecutively.

Thanks in advance for any help!

**Richard Buttrey** · 04-01-2014, 05:33 AM

Hi, and welcome to the forum.

I'd be inclined to use a helper column which counts the number of occurrencies of each value, then in a cell above the column use a

Formula:

Please Login or Register  to view this content.

formula to find the maximum number.

Wrap that MAX function inside an INDEX() function which will return the cell that has a maximum value and use a conditional format to highlight the cell if the count is greater than 3

You could also use a VBA approach to pop up a message box.

Please note. Don't upload pictures. We always prefer the actual workbook

**hemesh** · 04-01-2014, 05:50 AM

Pop up can be done with data validation
select the column Z then go to data validation
select custom and enter below
=COUNTIFS($A$1:$A1,$A1,$Z$1:$Z1,$Z1)<=3
go to error alert select warning from drop down and Type the title you want and message you want if you want to restrict the user to put the data then select stop.

In attached file data validation is in column B1 to B23.

**LewisK** · 04-01-2014, 06:29 AM

Thanks Guys for the quick responses and sorry Richard for the pic!

These work OK for finding 3 repeats in the entire list, but all I want to do is to warn if there are 3 consecutive repeated entries of the same machine name and and "0" result..

**LewisK** · 04-01-2014, 06:40 AM

I attached a simplified version of the workbook - in the example you can see that there are 4 instances of the machine being 'SL3' and a result of "0", but only the last 3 entries for the 'SL3' machine are consecutive. This is when I need a warning to appear. Thanks, hope this explains more clearly!