SAME DATA FOR A GROUP

Question

I have an Event ID and a State of Activity column. I need a formula to check to see if all records for the Event have the same State of Activity.

Answer 1

I need a formula to check to see if all records for the Event have the same State

Just my opinion.

If you have 100+ States, I don't see the need to list each and every one of them and give the same true/false info.

Would you want to use something more efficient?

Answer 2

Hi @TERI RICE

Try this formula in cell C2:

=LET(data, A2:B10, BYROW(data, LAMBDA(a, TAKE(COUNTIF(data, a),, 1) = TAKE(COUNTIF(data, a),, -1))))

or this formula (it is slightly shorter than the previous one):

=LET(data, A2:B10, cif, COUNTIF(data, data), TAKE(cif,, 1) = TAKE(cif,, -1))

HTH

IlirU

Answer 3

Hi,

In cell C2, enter this formula

=LET(a,A2:A10,b,B2:B10,COUNTIF(a,a)=COUNTIFS(a,a,b,b))

Hope this helps.

Answer 4

Dear Teri,

Assuming:

• Column A = Event ID
• Column B = State of Activity
• Column C = All Same

Use this in C2 and fill down:

=COUNTIF($A$2:$A$100,A2)=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

It compares how many rows share the Event ID against how many share both the Event ID and that row's State of Activity. If the two counts match, every record for that event has the same state.

Adjust the row range to cover your data, or convert the range to a table so it expands automatically.

If you prefer a version that ignores the count comparison, this also works and reads a little more directly:

=COUNTIFS($A:$A,A2,$B:$B,"<>"&B2)=0

I hope this helps. Let me know if you need anything else.

---

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.