Return 1 if criteria met or 0 if not - based on two criteria with both columns having multiple instances (duplicates).

[Squirl28]

Hi,

In column B I have ticket numbers and there are 12 instances of each ticket (may grow). In column D I have clients. In column E I have specific text (e.g., alert, no alert, etc). In column AM I need it to show 1 or 0. I want it to only count the first instance of colum B (ticket number) and in column E count first instance of “Alert”. I’m working with a table with 1000 + records.

Example: This range should never show more than 1 for 12 instance range. In other words return 1 the first time you see ticket and alert on same row.

B D E AM
12345 C1 No Alert 0
12345 C2 Alert 1
12345 C3 Alert 0
12345 C4 No Alert 0
12345 C5 No Alert 0
12345 C6 No Alert 0
....

 

[Aladin Akyurek]

I could only do a screenshot, your help is greatly appreciated.
https://www.dropbox.com/s/3nuob8pnlpndxgd/image001.png?dl=0

You can also place the sheet itself in the dropbox.

 

[Squirl28]

I noticed that if there are two “Alerts” in a row the formula works. However, if it says “Alert” then “No Alert” it will give me 1 instead of 0. As soon as I change it to “Alert” it gives the 2nd one and the following 0. However, I can’t change the data as that would skew it. Thanks for any assistance

 

[Aladin Akyurek]

I noticed that if there are two “Alerts” in a row the formula works. However, if it says “Alert” then “No Alert” it will give me 1 instead of 0. As soon as I change it to “Alert” it gives the 2nd one and the following 0. However, I can’t change the data as that would skew it. Thanks for any assistance

Please try to post the sample where the formula does not behave as you expect along with the desired results.

 

[Squirl28]

Here you go, here’s an example of it not showing 0 until the next Alert. It has to be alert twice in a row for the formula to carry out.

https://www.dropbox.com/s/bq6ht8ci4wg0z4c/Copy of Test.xlsx?dl=0

 

[Squirl28]

Here is updated sheet with desired count as well. “Alert” should only count once per ticket number. If there are no alerts for ticket then it should show 0. If there are multiple “alerts” per ticket it will only show 1 for the first one and then the rest 0.
https://www.dropbox.com/s/iwnuteugw2g5j4d/Copy of Copy of Test.xlsx?dl=0

 

[Aladin Akyurek]

Here is updated sheet with desired count as well. “Alert” should only count once per ticket number. If there are no alerts for ticket then it should show 0. If there are multiple “alerts” per ticket it will only show 1 for the first one and then the rest 0.
https://www.dropbox.com/s/iwnuteugw2g5j4d/Copy of Copy of Test.xlsx?dl=0

Thanks.

In D2 enter and copy down:

=IF(ISNA(MATCH(B2,$B$1:B1,0)),IF($C2="Alert",1,0),0)

 

[Squirl28]

Thank you! This appears to be working. Really appreciate your help.

 

[Aladin Akyurek]

Thank you! This appears to be working. Really appreciate your help.

You are welcome.

 

[Squirl28]

This almost worked perfectly, the one thing I noticed is that it only works if the first instance says “alert”. In some cases the first status may be no alert and the 3rd or 4th, etc. may say “alert”. I attached the desired outcome.
https://www.dropbox.com/s/p23e3kkcxkhxc0q/updated Test.xlsx?dl=0

 

[Aladin Akyurek]

This almost worked perfectly, the one thing I noticed is that it only works if the first instance says “alert”. In some cases the first status may be no alert and the 3rd or 4th, etc. may say “alert”. I attached the desired outcome.
https://www.dropbox.com/s/p23e3kkcxkhxc0q/updated Test.xlsx?dl=0

In D2 enter and copy down:

=IF($C2="alert",IF(COUNTIFS($B$2:B2,B2,$C$2:C2,"alert")=1,1,0),0)