SOLVED

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

Copper Contributor

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   
4 Replies
best response confirmed by venkatvedavyasan (Copper Contributor)
Solution

@venkatvedavyasan 

See the attached workbook.

@Hans Vogelaar 

 

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.

 

venkatvedavyasan_0-1680527155436.png

Not looking same as what you have got ?

 

venkatvedavyasan_1-1680527191344.png

 

How to achieve this ? 

@venkatvedavyasan

In entered the formula in B10, then filled it to the right to F10.

I copied B10:F10, then pasted to B12. B14, B16 and B18.

@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.

column value.png

 

1 best response

Accepted Solutions
best response confirmed by venkatvedavyasan (Copper Contributor)
Solution

@venkatvedavyasan 

See the attached workbook.

View solution in original post