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 end dt. D2 Search "soft" in entire column for" Microsoft"
Answer:
Array formula in cell F10:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.
Copy cell F10 and paste to cell range F10:H12.
Explaining array formula in cell F10
Step 1 - Find cells containing the search string
The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive), if string is not found the function returns an error value.  If the ISERROR function returns FALSE the logical expression returns TRUE.
ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE
becomes
ISERROR(SEARCH("soft", {"Microsoft"; "IBM"; "Microsoft"; "Microsoft"; "3M"; "GE"; "3M"; "GE"; "Microsoft"; "GE"; "Oracle"; "Oracle"; "3M"; "IBM"; "Microsoft"; "Microsoft"; "IBM"; "Microsoft"; "3M"; "IBM"; "GE"; "GE"; "Oracle"; "GE"; "IBM"; "IBM"; "GE"}))=FALSE
becomes
ISERROR({6; #VALUE!; 6; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 6; 6; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})=FALSE
becomes
{FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}=FALSE
and returns
{TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Step 2 - Find cells matching dates criteria
The following two logical expressions calculates which records have dates inside the given date range. These two arrays are multiplied to apply AND logic meaning if both logical expressions return TRUE the formula returns TRUE or the equivalent numerical value which is 1. 0 (zero) is FALSE.
($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29)
becomes
(40513<={40544; 40485; 40563; 40482; 40576; 40482; 40481; 40509; 40504; 40512; 40611; 40560; 40509; 40448; 40461; 40607; 40453; 40459; 40574; 40607; 40625; 40465; 40604; 40490; 40569; 40528; 40641})*(40575>={40544; 40485; 40563; 40482; 40576; 40482; 40481; 40509; 40504; 40512; 40611; 40560; 40509; 40448; 40461; 40607; 40453; 40459; 40574; 40607; 40625; 40465; 40604; 40490; 40569; 40528; 40641})
becomes
({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE})*({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE})
and returns
{1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 1; 1; 0}
Step 3 - Convert matching cells and cells containing search string into row numbers
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3). If the ISERROR function returns FALSE (zero) then the IF function returns the corresponding row number.
IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), "")
becomes
IF({1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), "")
becomes
IF({1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27}, "")
and returns
{1; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}
Step 4 - Return the k-th smallest number
The SMALL function returns the k-th smallest value in the array based on the COLUMN function and a relative cell reference. Then the cell is copied to cells below the relative cell reference changes, this makes the SMALL function return a new value in each cell.
SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), ""), ROW(A1))
becomes
SMALL({1; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}, ROW(A1))
becomes
SMALL({1; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}, 1)
and returns 1.
Step 5 - Return a value of the cell at the intersection of a particular row and column
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX($B$3:$D$29, SMALL(IF((ISERROR(SEARCH($G$3, $C$3:$C$29))=FALSE)*($G$4<=$B$3:$B$29)*($G$5>=$B$3:$B$29), MATCH(ROW($C$3:$C$29), ROW($C$3:$C$29)), ""), ROW(A1)), COLUMN(A1))
becomes
=INDEX($B$3:$D$29, 1, COLUMN(A1))
becomes
=INDEX($B$3:$D$29, 1, 1)
becomes
=INDEX({40544, "Microsoft", 72,9; 40485, "IBM", 11,1; 40563, "Microsoft", 29,6; 40482, "Microsoft", 44,1; 40576, "3M", 13,7; 40482, "GE", 87,9; 40481, "3M", 84,4; 40509, "GE", 93,1; 40504, "Microsoft", 32,9; 40512, "GE", 21,8; 40611, "Oracle", 19,6; 40560, "Oracle", 88; 40509, "3M", 25,2; 40448, "IBM", 52,1; 40461, "Microsoft", 26,4; 40607, "Microsoft", 76,4; 40453, "IBM", 55,7; 40459, "Microsoft", 37,6; 40574, "3M", 42,9; 40607, "IBM", 52,6; 40625, "GE", 54,7; 40465, "GE", 64,8; 40604, "Oracle", 25,6; 40490, "GE", 49,2; 40569, "IBM", 72,1; 40528, "IBM", 3,5; 40641, "GE", 53,6}, 1, 1)
and returns 40544 (1-Jan-2011).
Filter records category
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Lookup with criteria and return records.
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Excel categories
2 Responses to “Filter records based on a date range and a text string”
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 I have a similar issue. for example i have in column e currencies like usd yen pound
Is it possible to get currency results alphabetically sorted in addition with your results above ?
My goal is to have like a statement of account by currencies
Thank you
I want to search for a specific date, how should i change to get that?