Pulling Nonzero Values from a Column

M

mikesanto

New Member
Joined
Oct 12, 2007
Messages
1
Here's my problem. I have a column of roughly 1100 cells of data. Through a series of formulae and logic, I've reduced it to an identically sized column with most of the values zero, and 10-15 nonzero. What I want to do is pull all the nonzero values out of this column and record them in the next column over. I realize I could do this just by sorting the data, but I'd like to know if there's a way I can have excel do this step for me.

Any ideas?

Thanks!
 

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.
Sort by date Sort by votes
Hello Mike, welcome to MrExcel

I presume you want to put these values in one 10-15 cell range rather than separated by blanks?

assuming your data is in A1:A1200 then you could record all the non-zero numbers in descending order with this formula in B1 copied down to B20 or as far as you might need

=IF(COUNTIF(A$1:A$1200,">0")< ROWS(B$1:B1),"",LARGE(A$1:A$1200,ROWS(B$1:B1)))

or, if you want to show the numbers in the order they appear in the column try this formula

=IF(ROWS(B$1:B1)>COUNTIF(A$1:A$1200,">0"),"",INDEX(A$1:A$1200,SMALL(IF(A$1:A$1200>0,ROW(A$1:A$1200)-ROW(A$1)+1),ROWS(B$1:B1))))

This second one needs to be confirmed with CTRL+SHIFT+ENTER

edit: I'm assuming all your "nonzero" numbers are positive....or is that not the case?
 
Upvote 0
mikesanto

Welcome to the MrExcel board!

You didn't really make it clear if you wanted the numbers sorted or if that just happens to be the result of using a particular method. Anyway, here is another way. It may not suit because it also required manual steps, unlike Barry's, but you can decide.

I have set up dummy formulas in column E to get a list like you described. To get the list shown in column G, follow these steps:
1. Ensure heading in col E
2. Set up F1:F2 as shown
3. Select column E by clicking its heading label.
4. Data|Filter|Advanced Filter...|Copy to another location|Criteria range: F1:F2|Copy to: G1|OK

Excel Workbook
DEFG
1NumbersNumbersNumbers
21101
30015
415156
500-23
600
766
8-23-23
9
Non Zero Value
 
Upvote 0
I was able to use Barry's 2nd formula and it worked great, but I also want to bring in a corresponding date to my number that is in another column. Any help would be appreciated. Thanks.
 
Upvote 0
barry houdini said:
Hello Mike, welcome to MrExcel

I presume you want to put these values in one 10-15 cell range rather than separated by blanks?

assuming your data is in A1:A1200 then you could record all the non-zero numbers in descending order with this formula in B1 copied down to B20 or as far as you might need

=IF(COUNTIF(A$1:A$1200,">0")< ROWS(B$1:B1),"",LARGE(A$1:A$1200,ROWS(B$1:B1)))

or, if you want to show the numbers in the order they appear in the column try this formula

=IF(ROWS(B$1:B1)>COUNTIF(A$1:A$1200,">0"),"",INDEX(A$1:A$1200,SMALL(IF(A$1:A$1200>0,ROW(A$1:A$1200)-ROW(A$1)+1),ROWS(B$1:B1))))

This second one needs to be confirmed with CTRL+SHIFT+ENTER

edit: I'm assuming all your "nonzero" numbers are positive....or is that not the case?
Click to expand...

Hi

Just wondering how you would change the second formula to include negative numbers as well?

:confused:
 
Upvote 0
Tweetie85 said:
Hi

Just wondering how you would change the second formula to include negative numbers as well?

:confused:
Click to expand...

In the IF statement, where the logical test now reads as "IF(COUNTIF(A$1:A$1200,">0")," instead of ">0" [greater than zero], use "<>0" [not equal to zero].

Years too late, but there it is.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
2D spilled array, using REDUCE and INDIRECT to consolidate data from multiple sheets.
Replies
5
Views
257
Fluff
Fluff
E
  • Question
Macro to Pull Summary from Multiple Files
Replies
2
Views
238
dreid1011
dreid1011
N
  • Solved
Pulling certain row values if a number is matched
Replies
2
Views
152
Newbie73
N
R
  • Question
Best approach (VBA) to search through a sheet for all matches to a selected range (ie. multiple cells)?
Replies
2
Views
52
radiocam
R
M
  • Solved
Ignore Row with Text Using VLOOKUP
Replies
7
Views
374
Alex Blakenburg
A

Forum statistics

Threads
1,214,647
Messages
6,120,722
Members
448,987
Latest member
marion_davis

Share this page

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