Forum Discussion

venkatvedavyasan's avatar
venkatvedavyasan
Copper Contributor
Mar 30, 2023
Solved

How to extract a column value based on value in the range

I have a team ROTA in excel sheet. The first column is the Name and first row is the date. The intervening cells are loaded with values.

 

Date123456789101112131415161718192021222324252627282930
DaySatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSun
Person 1SP2222L222222M2222222PS2222222
Person 2222222L22L222222222222222222PS
Person 32222222222222NSP2222N222222222
Person 42222222PS222222222222222222222
Person 52222222SP222222222222222222222

 

I'm trying to find the dates for a particular person with particular value.

Please guide with a formular through which I can achieve this.

 

 SPLMN
Person 112714 
Person 12322   
Person 2  7  
Person 2302910  
Person 31516  14
Person 3    21
Person 498   
Person 589   
excel
Formulas and Functions
    • venkatvedavyasan's avatar
      venkatvedavyasan
      Copper Contributor
      Apr 03, 2023

      HansVogelaar 

       

      How did you get the Spill getting filled automatically ? Because when I copy person 1 to 5 in from A10-A14 I'm not getting the value as you got.

       

      Not looking same as what you have got ?

       

       

      How to achieve this ? 

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Mar 14, 2024

        venkatvedavyasan 

        =IFNA(REDUCE(HSTACK("","S","P","L","M","N"),A3:A7,LAMBDA(a,b,VSTACK(a,REDUCE(b,HSTACK("S","P","L","M","N"),LAMBDA(u,v,HSTACK(u,IFERROR(TOCOL(FILTER(B1:AE1,FILTER(B3:AE7,A3:A7=b)=v)),""))))))),"")

         

        With Office 365 or Excel for the web this formula could be an alternative.

         

Resources