Alert if a value repeats more than x times

[nupema]

Dear community,

I would like to have your help to find a solution for the following situation.

Basically I'm trying to have an alert if within a range (in a raw) if a value repeats more than x times, but only if a certain value is not found on the previous cell.

Meaning, I want an alert to come up if I have in the corresponding raw certain criteria. If a cell with "AB" is found and the next 6 cells in that RAW, after the "AB" have numbers only, the Cell where the formula is running should return a "NOT OK".

I'm for a while trying to make this working, but until now without success.. :/

Can someone kindly help me?

 

[Yongle]

I want an alert to come up if .... a cell with "AB" is found and the next 6 cells in that ROW, after the "AB" have numbers only, the Cell where the formula is running should return a "NOT OK".

=IF(AND(A2="AB",NOT(ISERR(B2+C2+D2+E2+F2+G2))),"Not OK","")

Above formula returns Not OK if A2 = "AB" and there no TEXT in range B2:G2

 

[nupema]

Hi Yongle,

Thanks for your reply. It's not exactly what I need, I guess I didn't explain my self so good.

Please check the file I'm sharing with an example of what I need and I hope you can help me.

 

[Fluff]

Cross posted Alert if a value repeats more than x times

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Yongle]

looking at your file ....

Is this what you want ?
- identify every row where 6 adjacent cells ALL contain a number > 0

 

[nupema]

Cross posted Alert if a value repeats more than x times

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Apologies. Here it goes the Link to the other forum.

 

[nupema]

looking at your file ....

Is this what you want ?
- identify every row where 6 adjacent cells ALL contain a number > 0

Yes

 

[Yongle]

I suspect that your problem could be solved using an ARRAY formula - but that is not one of my sterngths

In the meantime ....
This is not quite what you want but it uses condtional formatting to format the FIRST of 6 (or more) adjacent cells matching your requirement

Using example in your workbook (I have removed your formatting) ...

Select C3:P7
Click on Conditional Formatting (on Home Tab) \ New Rule \ Use Formula to determine ... (see below)
This formula in the box =AND(COUNT(C3:H3)=6,COUNT(B3:G3)<>6)
... then click on Format etc

As expected, C3 and G6 are formatted

It is simple and very close - but unfortunately there is no standard Excel formula to count red cells in a row

A User Defined Funcion can be written (using VBA) and used like any other formula - I will post UDF on the thread in approx 24 hours