Get only display values in a new column, and ignore blank cells

[kaulishmehta]

Hello,

Now as shown i want to try make my C column, look like my E column.
I tried this formula, but didnot work
=IF(ROWS(D$2:D3)>COUNTA(C:C),"",INDEX(C:C,SMALL(IF(C$2:C$20<>"",ROW(C$2:C$20)),ROWS(D$2:D3))))

if somebody can help me it will be great!

 

[kaulishmehta]

The D column is the result of my formula

 

[Fluff]

What is in col C, is it a formula?

 

[kaulishmehta]

yes, the column C is finding all the missing values that exist in column A and are not in column B.

 

[kaulishmehta]

yes, the column C is finding all the missing values that exist in column A and are not in column B.

sample_ordersheet.xlsx
	A	B	C	D	E
1	Sample No.	Sample No.	Pending List
2	0	0		0	453
3	453	452	453	453	543
4	452	24		0	2343352
5	543	465	543	543	2443
6	24	2335		0	243
7	465	5		0	3
8	2335	4		0
9	2343352	0	2343352	2343352
10	2443	0	2443	2443
11	243	0	243	243
12	5	0		0
13	4	0		0
14	3	0	3	3
15	0	0		0
16	0	0		0
17	0	0		0
18	0	0
19	0	0
20	0	0
21	0	0
22	0	0
23	0	0
OTHERS

Cell Formulas
Range	Formula
A1:A23	A1	=Sample!A1
B1:B23	B1	='Recieved Samples'!B1
C2:C23	C2	=IF(ISERROR(VLOOKUP(A2,B:B,1,FALSE)),A2, )
D2:D17	D2	=IF(ROWS(D$2:D2)>COUNTA(C:C),"",INDEX(C:C,SMALL(IF(C$2:C$20<>"",ROW(C$2:C$20)),ROWS(D$2:D2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Pending_List	=OTHERS!$C$2:$C$1048576	D2:D17

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C
1	Sample No.	Sample No.	Pending List
2	0	0	453
3	453	452	543
4	452	24	2343352
5	543	465	2443
6	24	2335	243
7	465	5	3
8	2335	4
9	2343352	0
10	2443	0
11	243	0
12	5	0
13	4	0
14	3	0
15	0	0
16	0	0
17	0	0
18	0	0
19	0	0
20	0	0
21	0	0
22	0	0
23	0	0
24
Result

Cell Formulas
Range	Formula
C2:C17	C2	=IFERROR(INDEX($A$2:$A$50,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$50,$B$2:$B$50,0))),ROWS(C$2:C2))),"")