List Duplicates in One Cell Based on Values in Another Cell

[renee_crozier]

I have a list of assets that I am trying to list those who don't have access to each in column F. If everyone has access, then return a blank.

I've tried different formulas but messed around with each to create a few iterations. The three main formulas I attempted to use were:

• =IF(E2>1, TEXTJOIN("; ",TRUE,FILTER(Owner, A2=ID)), "")
• =IF(FILTER(EditAccess, A2=ID)="Has Access", "", TEXTJOIN("; ",TRUE,FILTER(Owner, A2=ID))
• =IFS(AND(FILTER(EditAccess, A2=ID)="Has Access", E2>1), FILTER(Owner, A2=ID)))

Any help would be greatly appreciated. Clean up of data will be made using Power Query after this equation is figured out.

[#2 · Accepted solution]

@renee_crozier 

=TEXTJOIN("; ",,FILTER($B$2:$B$9,($A$2:$A$9=A2)*($D$2:$D$9="Does not have access"),""))

This returns the expected result in my sheet.

[#3]

Thank you!