Feel like I am getting close. The issue is that there are two cases. The case where the 'Last 4' is the same as the 'Account' as in row 2, and the case where the 'Last 4' is different from the 'Account' as in row 10. The formula that handles the case in row 10 is:
Formula:
=IF(OR(COUNTIFS(B$2:B$15,B2,A$2:A$15,"Y")=1,B2=B1),"",IF(B2<>D2,D2,""))
The array entered formula (see post #4 for activation) that handles the case in row 2, and the remainder of the yellow shaded table is:
Formula:
=IFERROR(IF(AND(COUNTIF($B$2:$B2,$B2)=1,INDEX($D$2:$D$13,LARGE(IF($B$2:$B$13=$B2,IF($A$2:$A$13="Y",ROW($A$2:$A$13)-1)),COUNTIFS($B$2:$B$13,$B2,$A$2:$A$13,"Y")-COLUMN(A1)))<>D2),INDEX($D$2:$D$13,LARGE(IF($B$2:$B$13=$B2,IF($A$2:$A$13="Y",ROW(A$2:A$13)-1)),COUNTIFS($B$2:$B$13,$B2,$A$2:$A$13,"Y")-COLUMN(A1))),""),"")
I am having a hard time getting the two married up, but will get back to work on this in the morning, provided someone else hasn't solved it by then.
Let us know if you have any questions.
Bookmarks