Extract records containing negative numbers
Table of Contents
Extract negative values and adjacent cells (array formula)
Extract negative values and adjacent cells (Excel Filter)
Array formula in B23:
copied down as far as needed and then copied to the right as far as needed.
Explaining array formula in cell B23
Step 1 - Filter negative values and return their corresponding row number
IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")
becomes
IF({1;5;0;-2;-5;2;4;4;-3;-4;-4;1;-1;0;-5;1}<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")
becomes
IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")
becomes
IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, "")
and returns
{"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}
Step 2 - Return the k-th smallest row number
SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1))
becomes
SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, ROW(A1))
becomes
SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, 1)
and returns 4.
Step 3 - Return value from cell range
INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))
becomes
INDEX($B$3:$D$18, 4, 1)
becomes
INDEX({"MARGARET","ROBINSON",1; "DAVID","DAVIS",5; "MICHAEL","JONES",0; "MARIA","GARCIA",-2; "LISA","RODRIGUEZ",-5; "PATRICIA","JACKSON",2; "MARY","THOMAS",4; "THOMAS","TAYLOR",4; "MICHAEL","JONES",-3; "RICHARD","MILLER",-4; "JOSEPH","MOORE",-4; "LINDA","WHITE",1; "BARBARA","HARRIS",-1; "MARY","THOMAS",0; "JOHN","JOHNSON",-5; "DOROTHY","CLARK",1} 4, 1)
and returns "Maria" in cell B23.
Download excel sample file for this tutorial.
extract negative values and adjacent cells.xls
(Excel 97-2003 Workbook *.xls)
Extract negative values and adjacent cells (Excel Filter)
- Select B2:D18
- Click "Data" tab
- Click "Filter" on the Ribbon
- Click Black triangle in cell D2
- Select "Number Filters"
- Click "Less Than..."
- Type 0 (zero)
- Click OK!
Functions in this article:
MATCH(lookup_value;lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value
IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SMALL(array,k) returns the k-th smallest row number in this data set.
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROW(reference) returns the rownumber of a reference
COLUMN(reference)
returns the column number of a reference
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Extract records where all criteria match if not empty
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Extract all rows that contain a value between this and that
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]
Extract records between two dates
Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]
Filter records based on a date range and a text string
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
5 Responses to “Extract records containing negative numbers”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
the formula for Extracting negative values and adjacent cells (array formula) is very useful for me and saved me a lot of time. Now I have one additional question - how can I change the formula to extract only positive value?
Thanks for help
Milan
good...but can you explan...itin detail
yashwant
I have added an explanation to this post.
Can you return this values in ascending or descending order
Yes, it is possible.
Ascending:
Descending:
These dynamic array formulas contain two new functions available for Excel 365 subscribers:
How to use the FILTER function
How to use the SORT function