Function to weed out certain numbers
Posted by Mike on September 19, 2001 10:58 AM
I have a worksheet that has a list of numbers ranging between -10 and 10. Suppose they are the following
10
5
-3
4
0
4
0
-2
I have two rows that have 0 for the value. I want another worksheet that will "filter" this list of numbers and only show the amounts that do not equal O. My new worksheet would look like this:
10
5
-3
4
4
-2
Is there a function that will do this?
Thanks
Posted by Eric on September 19, 2001 11:41 AM
Autofilter?
I know you want this to go to another sheet, but bear with me here.
Go to Data-->Filter-->autofilter, and in the drop down list in the top row of your column of numbers pick "custom", and choose "does not equal" and choose "0"
This will get you the list you want. you can then select and copy the list to a new worksheet, and then go back and turn off the autofilter on the original list (go Data-->Filter and uncheck the autofilter option).
I was able to make a pretty quick rudimentary macro (and if I made it, you can bet it was rudimentary!) that runs through these steps, placing the filtered list on the new page and then removing the autofilter from the original list.
HTH
Posted by Mike on September 20, 2001 4:26 PM
Not what I'm looking for
I really need it to go to another sheet without having to cut and paste
Posted by Aladin Akyurek on September 21, 2001 12:33 PM
A formula-based solution...
would be what follows.
I'll assume your sample data (source list) to be in A1:A8 in sheet WS1. You want the non-zero values from the source list to go to sheet WS2, say, in A from A1 on.
Activate Insert|Name|Define.
Enter MaxNum as name in the Names in Workbook box.
Enter the following formula in the Refers To box:
=9.99999999999999E+307
Activate Add. Don't leave yet the Define Name window.
Enter MaxRecs as name in the Names in Workbook box.
Enter the following formula in the Refers To box:
=MATCH(MaxNum,WS1!$A:$A)
Activate Add. Don't leave yet the Define Name window.
Enter SList as name in the Names in Workbook box.
Enter the following formula in the Refers To box:
=OFFSET(WS1!$A$1,0,0,MaxRecs,1)
Activate Add. Don't leave yet the Define Name window.
Enter NoZeroesList as name in the Names in Workbook box.
Enter the following formula in the Refers To box:
=OFFSET(WS2!$A$1,0,0,MaxRecs,1)
Activate OK.
In WS2,
in A1 array-enter: =IF(ROW()-ROW(NoZeroesList)+1 > ROWS(SList)-COUNTIF(SList,0), "", INDIRECT("WS1!" & ADDRESS(SMALL((IF(SList<>0, ROW(SList), ROW()+ROWS(SList))), ROW() - ROW(NoZeroesList) + 1), COLUMN(SList))))
Note. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.
Copy down the formula in A1 as needed.
Aladin
================
