How to find non-zero values and place them in the order they appear along with the name.

**IZAKK** · 01-14-2014, 10:21 PM

I am looking for some help. I have a sheet that lists all of the suppliers that we use (A2:A10).
I have code that pulls the total number of reject by month for each supplier (C2:C10).
What I would like is a formula that takes those values for the supplier and places them next to each other in columns E (see E2:E6) and does the same with the total reject values next to the applicable supplier (see F2:F6) without all of the zeros.
I think it is some kind of index and match function but cannot figure it out. I have attached an example of what I am trying to achieve.
Also, if at all possible, I would like to know if there is a way to auto populate a chart with those values and not the blanks that are generated (row 7 down in my example) so that my chart does not contain a bunch of blank space.

Any help would be greatly appreciated.

**benishiryo** · 01-14-2014, 10:30 PM

hi IZAKK. try this array formula in E2:
=IFERROR(INDEX($A$2:$A$10,SMALL(IF($C$2:$C$10<>0,ROW($A$2:$A$10)),ROWS(E$2:E2))-ROW($A$2)+1),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

if each supplier only appears once, then F2 can be:
=IF(E2="","",VLOOKUP(E2,A:C,3,0))

**IZAKK** · 01-14-2014, 10:52 PM

OMG! You are the Master! Thanks! I have been racking my brain over this for hours.

Any ideas on if there is a way to auto populate a chart with those values and not the blanks that are generated (row 7 down in my example) so that my chart does not contain a bunch of blank space.

**IZAKK** · 01-14-2014, 11:03 PM

Hi benishiryo,

I replied to your message but am not sure if I did it correctly because I could not find it in my sent items box. My actual file has more data but is laid out the same. Thanks again for all of your help!

**benishiryo** · 01-14-2014, 11:19 PM

you'll need 2 Named Ranges; for Column E & F. so press CTRL + F3 -> New -> Name: Supplier, Refers to:
=$E$2:INDEX($E:$E,COUNTA($E:$E))

repeat the steps & use Name as TotalRejects, Refers to:
=$F$2:INDEX($F:$F,COUNTA($E:$E))

select E1:F6 & create your chart. right-click the chart & Select Data. you should see 2 Edit buttons there. Edit both of them by changing the range to the Named Range. for eg, click Edit on the Legend Entries (Series) & you should see Series Values:
=Sheet1!$F$2:$F$6
retain the sheet name & the exclamation mark & change the range to the Named Range:
=Sheet1!TotalRejects

by the way, i don't think there's a Excel Vista? Vista is probably your Windows version. change it to Excel 2007, 2010 or 2013 (believe it's so because you uploaded an xlsx file). the version helps us identify which newer & more efficient formulas we can help you with. =)

**IZAKK** · 01-14-2014, 11:35 PM

Thanks! I updated my version of Excel.

I don't understand the first part though

. When do I press CTRL + F3? When I open the sheet? I tried it but it did not seem to do anything. Is it supposed to open an option for me to input the=$E$2:INDEX($E:$E,COUNTA($E:$E)) and=$F$2:INDEX($F:$F,COUNTA($E:$E)) formulas? I am working on a MAC could that be the issue?

**IZAKK** · 01-14-2014, 11:48 PM

Sorry, I found it. I had to go to Insert, Name, Define on my MAC. Thanks Again for all of your help!

How to find non-zero values and place them in the order they appear along with the name.

LinkBack

Thread Tools

Rate This Thread

Display

How to find non-zero values and place them in the order they appear along with the name.

Re: How to find non-zero values and place them in the order they appear along with the nam

Re: How to find non-zero values and place them in the order they appear along with the nam

Re: How to find non-zero values and place them in the order they appear along with the nam

Re: How to find non-zero values and place them in the order they appear along with the nam

Re: How to find non-zero values and place them in the order they appear along with the nam

Re: How to find non-zero values and place them in the order they appear along with the nam

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find MAX values in one column (A) and place data in another column next to the MAX value

[SOLVED] Find and list corresponding values in a column in descending order

To find pair of values in 2 columns (irrespective of order)

Formula to Find multiple values on table and place them in a cell.

Is there a way to place a row in numeric/alpheberical order?

Bookmarks

Bookmarks

Posting Permissions