Create a list from existing list ignoring zero values

[awanak]

i want to creat a list from existing list ignoring zero values

For Example

Original List	List to be created
Aslam	Aslam
Akram	Akram
0	Mohsin
Mohsin	Waseem
0
0
0
Waseem

<tbody>
</tbody>

 

[admiral100]

Hi,

Ctrl+Shift+Enter NOT just Enter

B2 =IFERROR(INDEX($A$2:$A$9,MATCH(0,IF($A$2:$A$9<>0,COUNTIF($B$1:B1,$A$2:$A$9)),0)),"")

	A	B
1	Original List	Unique List
2	Aslam	Aslam
3	Akram	Akram
4	0	Mohsin
5	Mohsin	Waseem
6	0
7	0
8	0
9	Waseem

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(1-($A$2:$A$9=0),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($1:1))),"")

 

[awanak]

Create a list on another from existing list on sheet 1 ignoring zero values

Hi,
I want to creat a list on another sheet from an existing list on sheet 1 or some other sheet ignoring zero values
Sheet-1 (Original List)

Sheet-1 (Original List)	Sheet-2 (List to be created)
Waseem	Waseem
Mohsin	Mohsin
0	Waqas
0	Vicky
Waqas	Zohra
Vicky
Zohra
0

<tbody>
</tbody>

 

[arthurbr]

Re: Create a list on another from existing list on sheet 1 ignoring zero values

Copy paste to the second sheet and filter out the null values

 

[awanak]

Re: Create a list on another from existing list on sheet 1 ignoring zero values

the original list is ever changing dear. need some other way out

 

[Peter_SSs]

Re: Create a list on another from existing list on sheet 1 ignoring zero values

Welcome to the MrExcel board!

A formula approach:
Adjust the Sheet1 ranges in this formula to at least cover that data and then copy the formula down.

Excel Workbook
	A
1	Waseem
2	Mohsin
3	Waqas
4	Vicky
5	Zohra
6
7
Sheet2

 

[arthurbr]

Re: Create a list on another from existing list on sheet 1 ignoring zero values

VBA solution using autofilter https://danwagner.co/how-to-delete-rows-with-range-autofilter/

 

[Peter_SSs]

Re: Create a list on another from existing list on sheet 1 ignoring zero values

VBA solution using autofilter https://danwagner.co/how-to-delete-rows-with-range-autofilter/

As far as I can see, that article does not address this issue:

the original list is ever changing..

 

[arthurbr]

Re: Create a list on another from existing list on sheet 1 ignoring zero values

Doesn't this part adapt the range's length ? ( still some tweaking needed of course)

[TABLE="class: highlight tab-size js-file-line-container"]
<tbody>[TR]
[TD="class: blob-code blob-code-inner js-file-line"]        lngLastRow = .Range("F" & .Rows.Count).End(xlUp).Row
[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="class: highlight tab-size js-file-line-container"]
<tbody>[TR]
[TD="class: blob-code blob-code-inner js-file-line"]        Set rngData = .Range("F5:F" & lngLastRow)[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
   
[TABLE="class: highlight tab-size js-file-line-container"]
<tbody>[TR]
[TD="class: blob-code blob-code-inner js-file-line"][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]