# Help with Excel formula please

Hi all,

I have a dataset that looks like the following and I'm trying to eliminate the blanks and consolidate the staff name into Column PM1 and PM2.

For PM 1 I use this formula:

=IF(A2<>"","Simone",IF(B2<>"","Clare",IF(C2<>"","Peter",IF(D2<>"","Jane",""))))

But I am at a loss with PM 2. The logic is similar but PM2 cannot be equal to PM1. So for the first row is PM2 should be Clare. Is there a way to formulate the row so if result =PM1 move on down to the next name?

 Simone Clare Peter Jane PM 1 PM2 Y Y Simone Y Y Simone Y Y Simone Y Y Clare Y Y Peter Y Clare Y Jane

Thank you guys.

Diana

2 Replies

# Re: Help with Excel formula please

Please consider the solution in the the attached workbook. Rather than using "Y"-characters in different positions, I find it so much easier to work with numbers 1 and 2.

By the way, I introduced a named range "Staff". You may replace it with "A1:D1" if you prefer.

# Re: Help with Excel formula please

As variant for PM1

``=INDEX(\$A\$1:\$D\$1,LARGE((\$A2:\$D2="Y")*COLUMN(\$A\$2:\$D\$2),IF(COUNTIFS(\$A2:\$D2,"Y")>1,2,1)))``

PM2

``=IF(COUNTIFS(\$A2:\$D2,"Y")=1,"",   INDEX(\$A\$1:\$D\$1,LARGE((\$A2:\$D2="Y")*COLUMN(\$A\$2:\$D\$2),1)))``