Populate cells based on content

[jebindavidson]

Hello There,

Here's what I'm working with,

1. Column A has Clause No
2. Columns B, C, D would be Team names

Each team would mark their compliance as Yes/No against Clause No.

Expected Result: Columns H, I, J would be the team names. This should be populated automatically with Clause No's that are marked No.

I was able to do this with a Pivot table. Maybe there is a better way of doing this.

I have attached a sample file.

Your help is much appreciated!

[Glenn Kennedy]

Use this:

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW(B$2:B$20)/(B$2:B$20="No"),ROWS(H$2:H2))),"")

copied across and down.

[jebindavidson]

Hello Glenn. Thanks for your help.

Now it's my job to go learn what Aggregate and Index does. I've never used these functions in my life.

If you would be so kind, could you please tell me how this formula works. I would be so grateful.

[Glenn Kennedy]

Thank fo rthe feedback. By way of explanation


=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW(B$2:B$20)/(B$2:B$20="No"),ROWS(H$2:H2))),"")

Red. where this condition is met
Orange. return the row number
Green smallest to largest
Cyan in ascending order 1,2,3, etc
Blue return the corresponding value from this column
Black return a blank instead of an error message when no more matches are found.

[jebindavidson]

Thanks for taking the time to explain! You've been a great help.

Cheers!

[Glenn Kennedy]

No problem...