process subgroups

Danny54

Active Member
Joined
Jul 3, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon

how would I go about doing the following

Do for each group; if the word Active is found in that grouping; set all of row H for that grouping to "Yes"

Groups shown below are 1, 2, 3, 4 - There could be many more groups

Ultimately, i'm looking to set the value Yes for each group that contains Active in the status field

Sheet1
#NameStatusDateCheckerGroupCountedResult
1​
John
1/1/2020​
Sally
1​
Y
1​
JoeActive
1/1/2021​
Sally
1​
Y
1​
Sam
1/1/2022​
Sally
1​
Y
2​
JohnActive
1/1/2020​
Sally
2​
Y
2​
Joe
1/1/2021​
Sally
2​
Y
2​
Sam
1/1/2022​
Sally
2​
Y
3​
John
1/1/2020​
Sally
3​
Y
3​
Joe
1/1/2021​
Sally
3​
Y
3​
Sam
1/1/2022​
Sally
3​
Y
4​
John
1/1/2020​
Sally
4​
Y
4​
JoeActive
1/1/2021​
Sally
4​
Y
4​
SamActive
1/1/2022​
Sally
4​
Y
4​
Kate
1/1/2022​
Sally
4​
Y



Sheet1 updated
#NameStatusDateCheckerGroupCountedResult
1​
John
1/1/2020​
Sally
1​
YYes
1​
JoeActive
1/1/2021​
Sally
1​
YYes
1​
Sam
1/1/2022​
Sally
1​
YYes
2​
JohnActive
1/1/2020​
Sally
2​
YYes
2​
Joe
1/1/2021​
Sally
2​
YYes
2​
Sam
1/1/2022​
Sally
2​
YYes
3​
John
1/1/2020​
Sally
3​
Y
3​
Joe
1/1/2021​
Sally
3​
Y
3​
Sam
1/1/2022​
Sally
3​
Y
4​
John
1/1/2020​
Sally
4​
YYes
4​
JoeActive
1/1/2021​
Sally
4​
YYes
4​
SamActive
1/1/2022​
Sally
4​
YYes
4​
Kate
1/1/2022​
Sally
4​
YYes

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
How about this?

Book1.xlsm
ABCDEFGHI
2#NameStatusDateCheckerGroupCountedResultFormula
31John1/1/2020Sally1YYesYes
41JoeActive1/1/2021Sally1YYesYes
51Sam1/1/2022Sally1YYesYes
62JohnActive1/1/2020Sally2YYesYes
72Joe1/1/2021Sally2YYesYes
82Sam1/1/2022Sally2YYesYes
93John1/1/2020Sally3Y 
103Joe1/1/2021Sally3Y 
113Sam1/1/2022Sally3Y 
124John1/1/2020Sally4YYesYes
134JoeActive1/1/2021Sally4YYesYes
144SamActive1/1/2022Sally4YYesYes
154Kate1/1/2022Sally4YYesYes
Sheet1
Cell Formulas
RangeFormula
I3:I15I3=IF(ISERROR(XMATCH("Active",FILTER($C$3:$C$15,$A$3:$A$15=F3))),"","Yes")
 
Upvote 0
Solution
Thanks for the help. Worked as suggested.

Have a great day
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top