Help with Excel formula please

Copper Contributor

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?

 

SimoneClarePeterJanePM 1PM2
YY  Simone 
Y Y Simone 
Y  YSimone 
 YY Clare 
  YYPeter 
 Y  Clare 
   YJane 

 

Thank you guys.

 

Diana

2 Replies

@DianaT 

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.

 

 

 

@DianaT 

As variant for

image.png

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)))