Creating a Helper Column based on two factors

Staylo95

I need to create 2 reports based on the sample below. One that shows the fields in yellow and one in white. The data in these changes quarterly.

Only 1 account per household will require quarterly reporting (which is done in other fields not shown here) but when I pull this into a report I want to see the values associated with entire household.

I've gotten as far as creating a helper column to count whether the Household ID appears more then one in the column but I can't figure out how to incorporate the Quarterly Reporting column.

There is logic and user instructions based on the Quarterly Reporting field so I can't just put "Yes" in each cell for the household.

Summer

@Staylo95

Using a count function to check a box if there are duplicate Household IDs and then filtering your report to only pull rows that are checked may be a good approach for you.

On your main sheet, you'd add a column to look for duplicates and count them. Depending on how your sheet is set up, you can make this a column formula, lock the column and then can hide it. Same with the checkbox column. This would also allow the formulas to run behind the scenes without you needing to maintain them.

In the Duplicate? column: =COUNTIF([Household ID]:[Household ID], [Household ID]@row)

Count how many instances the "Household ID" in that row are found in the "Household ID" column.

In the Duplicate Checkbox column: =IF([Duplicate?]@row > 1, 1, 0)

If the duplicate column in that row has a value greater than 1, check the box, otherwise leave unchecked.