IF/COLLECT? Report?

HHansford

Hello,

I'm at a loss as to how to solve my problem here.

I have a sheet that contains multiple rows that are identical except for the person they are assigned to (Instructor) and if the Confirmed box is checked or not. See example.

I'm thinking of these as groups of rows. For each group I only want to confirm one row from the group to one person. This has been a manual process.

I'd like a formula (or maybe a report is better?) to evaluate if I've missed confirming any of the groups of rows. My thought process is that the formula should first identify if the row belongs to a group and then if there is a Confirmed box checked within that group. No idea how to go about this.

Any ideas?

Hannah

Paul Newcome

Something like this in a flag column should flag all rows within a group that do NOT have at least one row Confirmed.

=IF(COUNTIFS([Start Date]:[Start Date], @cell = [Start Date]@row, Status:Status, @cell = Status@row, Location:Location, @cell =Location@row, Country:Country, @cell = Country@row, Confirmed:Confirmed, @cell = 1) = 0, 1)

Jon Mark H

Hey @HHansford !

I'm not quite understanding and could use a little clarification -

When you are saying "Groups of rows" what are you defining as the "Group" - the "Start Date"? or is it everything in the row before the "Instructor" column? (e.g. if it's just off of "Start Date", rows 3-5 in your snapshot are a "Group")

Either way let me know - it's definitely doable to use some helper columns & conditional formatting which would highlight any groups that don't have a "Confirmed" box checked.

-Jon

HHansford

Hi @Jon Mark H,

Good question! A "group of rows" would be determined by the Start Date, Status, Course, Location and Country ALL matching.

In the example there are 7 "groups"

Thanks

Paul Newcome

Something like this in a flag column should flag all rows within a group that do NOT have at least one row Confirmed.

=IF(COUNTIFS([Start Date]:[Start Date], @cell = [Start Date]@row, Status:Status, @cell = Status@row, Location:Location, @cell =Location@row, Country:Country, @cell = Country@row, Confirmed:Confirmed, @cell = 1) = 0, 1)

HHansford

@Paul Newcome - this works perfectly when combined with a filter - thank you!

Paul Newcome

Happy to help. 👍️