Forum Discussion

DianaT's avatar
DianaT
Copper Contributor
Dec 12, 2019

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?

 

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

 

Thank you guys.

 

Diana

Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 12, 2019

    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.

     

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Dec 12, 2019

    DianaT 

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

Resources