Formula to pull specific value from multiple similar records

Hello,

I have a table that I use to track sales records that I'm trying to add a way for our sales team to identify records that need follow-up.

There may be multiple records that all apply to the same group in my data, I am currently counting the groups using a countif to get a total number of groups.
I would like to add a column to pull in the status, if one of the records within the group reads Active, I want to show all of the records as Active. Is this something that can be done? The sample file is what I ultimately want to see.

Thank you in advance.

FormulaTest.xlsx

Try

=IF(COUNTIFS(A:A,A2,D:D,"Active"),"Active",D2)

In addition, if your data is always sorted by group, then =IF(A1=A2,0,1) will be a more efficient counter in column B than countif with an expanding range, although problems don't usually manifest themselves until you hit around 10-20k rows.

Great, thank you. Unfortunately the data isn't always sorted by group but thank you for that as well.