Formula to Create a List from and Existing List ignoring zero values

weso2k

New Member
Joined
Oct 21, 2014
Messages
3
Good Morning All,

I found a formula on line to eliminate blank cells in an original list and create a list without the blank cells, this formula was =IFERROR(INDEX(AreaBlanksRange,SMALL((IF(LEN(AreaBlanksRange),ROW(INDIRECT("1:"&ROWS(AreaBlanksRange))))),ROW(A1)),1),"")

I created a data ranges for this formula to read from, The problem I have is that the original list I have contains formulas in every cell, so excel does not pick up the cells as blank cells, I can set my formulas in my original list so that instead of appearing blank it shows a 0 in the cell instead, can the above formula be adapted to ignore 0 values instead of blank cells?


Example below of my original list and how I want the new list to appear:</SPAN>


Original List</SPAN>

20
0
30
0
40
0</SPAN>
Required List</SPAN>
20
30
40

I would also need this list to work for a formula picking up text, but would still show a 0 if I want to ignore this cell :)

Thanks

Mark </SPAN>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
[1]
[2]
100
30
100
0
40
30
30
100
40
0
40
0

<TBODY>
</TBODY>

[1] C1, just enter and copy down:
Rich (BB code):
=IFERROR(SMALL($A$2:$A$7,FREQUENCY($A$2:$A$7,0)+ROWS($C$2:C2)),"")

[2] D1, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX($A$2:$A$7,SMALL(IF(ISNUMBER(1/$A$2:$A$7),IF($A$2:$A$7>0,
  ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($D$2:D2))),"")

The second formula keeps the occurrence order, while the firs one does not.
 
Last edited:
Upvote 0
Hi Aladin

Thank you for the quick reply this is great for my numbers column, can this also be adapted somehow for a word list, I cant see how to up load a sample spread sheet but for example I have 2 columns A and B with formulas reading from another page,

the original list would look as follows:

Column A Column B
SCOTS 1 42
SCOTS 1 60
0 0
0 0
SCOTS 2 30
0 0
SCOTS 3 20

I would need my new lists to appear as

Column A Column B
SCOTS 1 42
SCOTS 1 60
SCOTS 2 30
SCOTS 3 20

I have got column B working from your formula, but I can not see how to get column A to work, I have set the formula in column A to say for example =IF(B2>0,'PALLET SHEET'!$B$13,"0")

So it will either display the area name or a zero.

Thanks

Mark
 
Upvote 0
Field-1Field-2 Field-1>0 Field-2
SCOTS-142 SCOTS-142
SCOTS-160 SCOTS-160
00 SCOTS-230
00 SCOTS-320
SCOTS-230
00
SCOTS-320

<COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5660" width=159><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2588" width=73><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3384" width=95><TBODY>
</TBODY>

D2, control+shift+enter (CSE), not just enter, and copy down:
Rich (BB code):
=IF($E2="","",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$E2,
  ROW($A$2:$A$8)-ROW($A$2)+1),COUNTIF($E$2:E2,E2))))

E2, CSE and copy down:
Rich (BB code):
=IFERROR(INDEX($B$2:$B$8,SMALL(IF(ISNUMBER(1/$B$2:$B$8),
  IF($B$2:$B$8>0,ROW($B$2:$B$8)-ROW($B$2)+1)),ROWS($E$2:E2))),"")
 
Upvote 0
Aladin you are a legend, I can't thank you enough for this one you have an excellent mind and have saved me from going insane :)
 
Upvote 0
Hi Aladin, can we amend the formula to sort the data from high to low, Thanks for the details above it really helped me
 
Upvote 0
Hi Aladin, can we amend the formula to sort the data from high to low, Thanks for the details above it really helped me


Book1
ABCDE
1Field-1Field-2Field-1>0 Field-2
2SCOTS-142SCOTS-160
3SCOTS-160SCOTS-142
400SCOTS-230
500SCOTS-320
6SCOTS-230
700
8SCOTS-320
Sheet2


In D2 control+shift+enter, not just enter, and copy down:

=IF($E2="","",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$E2,ROW($A$2:$A$8)-ROW($A$2)+1),COUNTIF($E$2:E2,E2))))

which is the same as before, that is, unmodified.

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(LARGE(IF(ISNUMBER(1/$B$2:$B$8),$B$2:$B$8),ROWS($E$2:E2)),"")
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top