Question about only showing a list of values which are not 0. Thanks! learning a lot! trying to improve my skills!

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
HI guys!!
Hope everyone is well. I am just trying to improve some skills and everyone here has been so kind!! Means a lot!!!
Quick question

If I have a list like this

Apple: 2
Orange: 3
Peach: 0
Banana: 1
Grape: 0
Mango 0
Papaya: 1


What formula can I use to make the list remove the values which are zero... so in the end it will look like

Apple: 2
Orange: 3
Banana: 1
Papaya: 1

Thanks :) :) whoever can help me with this rocks :)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

caabyyc

Board Regular
Joined
Oct 24, 2013
Messages
126
a1 1a11
b2 2b22
c3 3c33
d0 e84
e8 5g85
f0 6
g8 7 7
ABCDEF
D1=IF(B1=0,"",ROW())
E1=IF(ROW(A1)>COUNT($D:$D),"",INDEX(A:A,SMALL($D:$D,ROW(A1))))

<colgroup><col style="width: 16pt; mso-width-source: userset; mso-width-alt: 739;" width="21"> <col style="width: 18pt; mso-width-source: userset; mso-width-alt: 853;" width="24"> <col style="width: 19pt; mso-width-source: userset; mso-width-alt: 910;" width="26"> <col style="width: 13pt; mso-width-source: userset; mso-width-alt: 625;" span="3" width="18"> <col style="width: 21pt; mso-width-source: userset; mso-width-alt: 995;" width="28"> <col style="width: 11pt; mso-width-source: userset; mso-width-alt: 512;" width="14"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>





HI guys!!
Hope everyone is well. I am just trying to improve some skills and everyone here has been so kind!! Means a lot!!!
Quick question

If I have a list like this

Apple: 2
Orange: 3
Peach: 0
Banana: 1
Grape: 0
Mango 0
Papaya: 1


What formula can I use to make the list remove the values which are zero... so in the end it will look like

Apple: 2
Orange: 3
Banana: 1
Papaya: 1

Thanks :) :) whoever can help me with this rocks :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
HI guys!!
Hope everyone is well. I am just trying to improve some skills and everyone here has been so kind!! Means a lot!!!
Quick question

If I have a list like this

Apple: 2
Orange: 3
Peach: 0
Banana: 1
Grape: 0
Mango 0
Papaya: 1


What formula can I use to make the list remove the values which are zero... so in the end it will look like

Apple: 2
Orange: 3
Banana: 1
Papaya: 1

Thanks :) :) whoever can help me with this rocks :)

C2, control+shift+enter, not just enter, copy across to D2, and copy down:
Rich (BB code):
=IFERROR(INDEX(A$2:A$8,SMALL(IF(ISNUMBER(1/$B$2:$B$8),
  ROW(A$2:A$8)-ROW(A$2)+1),ROWS(C$2:C2))),"")
 

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
Hi! how are you doing? Thanks a lot for your help!!
I have a few questions from what you did

In column f how did you order everything perfectly? what was your formula there?
Thanks a bunch :) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top