Create a list from existing list ignoring zero values

[awanak]

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

For Example [TABLE="width: 500"]
<tbody>[TR]
[TD]Original List[/TD]
[TD]List to be created[/TD]
[/TR]
[TR]
[TD]Aslam[/TD]
[TD]Aslam[/TD]
[/TR]
[TR]
[TD]Akram[/TD]
[TD]Akram[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Mohsin[/TD]
[/TR]
[TR]
[TD]Mohsin[/TD]
[TD]Waseem[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Waseem[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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)),"")

[TABLE="width: 232"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Original List[/TD]
[TD]Unique List[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aslam[/TD]
[TD]Aslam[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Akram[/TD]
[TD]Akram[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]Mohsin[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mohsin[/TD]
[TD]Waseem[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Waseem[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet-1 (Original List)[/TD]
[TD]Sheet-2 (List to be created)[/TD]
[/TR]
[TR]
[TD]Waseem[/TD]
[TD]Waseem[/TD]
[/TR]
[TR]
[TD]Mohsin[/TD]
[TD]Mohsin[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Waqas[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Vicky[/TD]
[/TR]
[TR]
[TD]Waqas[/TD]
[TD]Zohra[/TD]
[/TR]
[TR]
[TD]Vicky[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zohra[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]