Filter or Formula for Processing Work

[Vishaal]

HI, we have the following sheet where we want to check the steps where are work is pending, kindly provide any excel formula

we have the excel 2007, 2010

sheet

New Microsoft Excel Worksheet - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Work	Step 1			Step 2			Step 3			Step 4			Step 5			Step 6
2		Date	Status	Late	Date	Status	Late	Date	Status	Late	Date	Status	Late	Date	Status	Late	Date	Status	Late
3	John	21/08/2012	Done		23/08/2012	Done		26/08/2012	Done		28/08/2012	Done		30/08/2012
4	Dan	24/08/2012	Done		26/08/2012	Done		27/08/2012	Done		28/08/2012
5	Ronshi	24/08/2012	Done		26/08/2012	Done		27/08/2012
6	Pome	21/09/2012	Done		23/09/2012	Done		26/09/2012	Done		28/09/2012	Done		30/09/2012	Done		1/10/2012
Sheet6

Required Result Sheet

New Microsoft Excel Worksheet - Copy.xlsx
	A	B
26	Status of Step
27	John	Step 5
28	Dan	Step 4
29	Ronshi	Step 3
30	Pome	Step 6
Sheet6

thanks

 

[JEC]

Hi try

Book1
	A	B
10	John	Step 5
11	Dan	Step 4
12	Ronshi	Step 3
13	Pome	Step 6
Sheet1

Cell Formulas
Range	Formula
B10:B13	B10	=LOOKUP(2,1/(B3:S3<>""),"Step "&INT((COLUMN($B$1:$S$1)-2)/3)+1)

 

[Bosquedeguate]

Or you might try this ...
=INDEX(A$1:R$1,MATCH(MAX(B3,E3,H3,K3,N3,Q3),B3:R3))

Book2 (Autosaved).xlsm
	A	B
12	Work Status
13	John	Step 5
14	Dan	Step 4
15	Ronshi	Step 3
16	Pome	Step 6
Sheet7

Cell Formulas
Range	Formula
A13:A16	A13	=A3
B13:B16	B13	=INDEX(A$1:R$1,MATCH(MAX(B3,E3,H3,K3,N3,Q3),B3:R3))

 

[Vishaal]

Thansk Bosquedeguate​

Thanks JEC​

your formula helped me.