if or filter or any other formula/code

[Vishaal]

Hi All,

Thanks in advance, we have the following sheet

Item Code	Work 1			Work 2			Work 3			Work 4
	P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3
A-405	fs			xvxv
A-820	fdsf	dfdf	ok	vxvx
A-440	fdsf	fds	ok	vxvx	fsdssf	ok	fdaf
A-650	vxv	vxv	ok	xcvv	gsds	ok	vcxxv	vxc	ok	gdg
A-260	cxzc	cx	ok	vcxv	xvxc	ok	cvv	vxcv	ok	dgdg	gdgdg	ok

and want to do following work

Process - In a new sheet we want to check only work which is not done, we mean

1. We will check first "Work 1" done or not, you can see that D3 is not o.k., so it will show in new sheet with item code
2. if work 1 done than We will check second "Work 2" done or not, you can see that G4 is not o.k., so it will show in new sheet with item code
3. if work 1 and work 2 done than We will check second "Work 3" done or not, you can see that J5 is not o.k., so it will show in new sheet with item code
4. if work 1, work2 and work 3 done than We will check second "Work 4" done or not, you can see that M6 is not o.k., so it will show in new sheet with item code

i have the headings work 1 to Work 25, how can we do that, help pls

Sample result is

Item Code
A-405	Work 1 - Pending	or we can show the value B3 here
A-820	Work 2 - Pending	or we can show the value E4 here
A-440	Work 3 - Pending	or we can show the value H5 here
A-650	Work 4 - Pending	or we can show the value K6 here

can we do this through formula, if not then code pls,

 

[shaowu459]

Just for your reference:

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	Work 1			Work 2			Work 3			Work 4
2		P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3
3	A-405	fs			xvxv
4	A-820	fdsf	dfdf	ok	vxvx
5	A-440	fdsf	fds	ok	vxvx	fsdssf	ok	fdaf
6	A-650	vxv	vxv	ok	xcvv	gsds	ok	vcxxv	vxc	ok	gdg
7	A-260	cxzc	cx	ok	vcxv	xvxc	ok	cvv	vxcv	ok	dgdg	gdgdg	ok
8
9	Item Code	Status
10	A-405	Work 1 - Pending
11	A-820	Work 2 - Pending
12	A-440	Work 3 - Pending
13	A-650	Work 4 - Pending
Sheet1

Cell Formulas
Range	Formula
A10:A13	A10	=IF(M3="",A3,"")
B10:B13	B10	=IF(A10="","",INDEX($1:$1,MIN(IF((B3:M3="")*(B$2:M$2="P-3"),COLUMN(B:M)))-2)&" - Pending")
Press CTRL+SHIFT+ENTER to enter array formulas.

Above formula will leave blanks for some cases:

 

[Vishaal]

U have updated this for Till column M, but we need to this till column nd

In that case what modifications required, guide pls

 

[shaowu459]

=IF(A10="","",INDEX($1:$1,MIN(IF((B3:nd3="")*(B$2:nd$2="P-3"),COLUMN(B:nd)))-2)&" - Pending")

 

[Vishaal]

Thanks for the help shaowu459

 

[shaowu459]

Thanks for the help shaowu459

You are welcome

 

[Vishaal]

Hi shaowu459,

we are facing one problem and that is when all cells are filled except G3, below formula is not working

=IF(M3="",A3,"")

so for getting the value from A column, pls suggest some other formula or modify current formula, basically we can check any one condition from here
=if(column C, F, I, L have any blank cell then we will get the value here)
or
=if(column D, G, J, M have any blank cell then we will get the value here)
or
=if(column B have value and C is blank / column E have value and F is blank / column H have value and I / column K have value and L is blank is blank) then we will get the value here)

 

[Vishaal]

one more criteria we are adding for calculation A10, it will check =IF(M3:M="",A3:A,"") and return value only who can pass

 

[shaowu459]

How about this one?

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	Work 1			Work 2			Work 3			Work 4
2		P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3
3	A-405	fs			xvxv
4	A-820	fdsf	dfdf	ok	vxvx	A	A	A	A	A	A	A	OK
5	A-440	fdsf	fds	ok	vxvx	fsdssf	ok	fdaf
6	A-650	vxv	vxv	ok	xcvv	gsds	ok	vcxxv	vxc	ok	gdg
7	A-260	cxzc	cx	ok	vcxv	xvxc	ok	cvv	vxcv	ok	dgdg	gdgdg	ok
8
9	Item Code	Status
10	A-405	Work 1 - Pending
11
12	A-440	Work 3 - Pending
13	A-650	Work 4 - Pending
Sheet1

Cell Formulas
Range	Formula
A10:A13	A10	=IF(B10="","",A3)
B10:B13	B10	=IFERROR(INDEX($1:$1,MIN(IF((B3:ND3="")*(B$2:ND$2="P-3"),COLUMN(B:ND)))-2)&" - Pending","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item Code	Work 1			Work 2			Work 3			Work 4
2		P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3	P-1	P-2	P-3
3	A-405	fs	ee		xvxv	B	B	B	B	B	B	B	OK
4	A-820	fdsf	dfdf	ok	vxvx	A	A	A	A	A	A	A	OK
5	A-440	fdsf	fds	ok	vxvx	fsdssf	ok	fdaf
6	A-650	vxv	vxv	ok	xcvv	gsds	ok	vcxxv	vxc	ok	gdg
7	A-260	cxzc	cx	ok	vcxv	xvxc	ok	cvv	vxcv	ok	dgdg	gdgdg	ok
8
9	Item Code	Status
10	A-405	Work 1 - Pending
11
12	A-440	Work 3 - Pending
13	A-650	Work 4 - Pending
Sheet1

Cell Formulas
Range	Formula
A10:A13	A10	=IF(B10="","",A3)
B10:B13	B10	=IFERROR(INDEX($1:$1,MIN(IF((B3:ND3="")*(B$2:ND$2="P-3"),COLUMN(B:ND)))-2)&" - Pending","")
Press CTRL+SHIFT+ENTER to enter array formulas.