TEXT SEARCH

R

roy.okinawa

Here is what I have:

Column J is the date opened. Column T is the date closed. Of course,
column T will have no date if still open.
Column K and O may or may not have text.
Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
search.

What I need is a formula in AF8 that will look at the date entered in AF6
and find a match in columns J and T. Then it will look to see if there is
text in K and O. If so, populate AF8 with that text.
 
G

Govind

Hi,

What if the same date is there in Column J and Column T? How do you
want your formula to work in that case?

Regards

Govind.
 
G

Govind

Hi Roy,

Try this formula

=OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6))-1,2)&OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6))-1,3)

entered with CTRL+SHIFT+ENTER.

Here J1:J100 is the range with date opened and T1:T100 is the range with
date closed. Change the formula to suit your actual range.

Regards

Govind.
 
R

roy.okinawa

I don't see any reference to Columns K or O in the formula. Is this formula
going to search for the text in those columns?

Roy
 
G

Govind

Hi,

Its not explicitly mentioned but are worked out by the formula. But my
reference wasnt correct in my earlier formula and hence use this

=OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6))-1,1)&OFFSET(J1,MATCH(1,(J1:J100=AF6)+(T1:T100=AF6))-1,5)

The offset formula starts in column J, matches the given date, goes that
row and then moves 1 column right to refer to column K in the first
formula. In the second one, it moves 5 column right to refer to column
O. Change J1:J100 and T1:T100 to the actual range.



Regards

Govind.
 
R

roy.okinawa

Govind,

It is not working. The text in columns K and O are not being brought
across.

Thanks,

Roy
 
G

Govind

Hi,

Sorry about that. Can you mail me your spreadsheet to my email id please.

Regards

Govind.
 
M

Max

Perhaps try also, in AF8 (normal ENTER):

=IF(AF6="","",IF(ISNA(MATCH(AF6,$J$8:$J$100,0)),IF(ISNA(MATCH(AF6,$T$8:$T$10
0,0)),"No date matches found",INDEX(TRIM($K$8:$K$100&"
"&$O$8:$O$100),MATCH(AF6,$T$8:$T$100,0))),INDEX(TRIM($K$8:$K$100&"
"&$O$8:$O$100),MATCH(AF6,$J$8:$J$100,0))))

Some assumptions:
---------------------------
Source range in cols J, K, O, T is within row 8 to row 100
Unique dates in cols J and T
Sequential checks: Col J is checked ahead of col T
Returns of text is required from both cols K and O, separated by a space
Any unmatched dates will return: "No date matches found"

Adapt to suit ..

A sample construct is at:
http://cjoint.com/?bFrwPxPItm
roy_okinawa_TextSearch_wks.xls
 
R

roy.okinawa

Max,

Thanks. This is just what I was looking for. It is working just fine.
However, although Columns J and T have unique dates, m/dd/yyyy, I would like
to enter mm/yyyy in AF6 as the search criteria.

Roy
 
M

Max

roy.okinawa said:
Thanks. This is just what I was looking for.
It is working just fine.
However, although Columns J and T have unique dates,
m/dd/yyyy, I would like to enter mm/yyyy
in AF6 as the search criteria.
Click to expand...

That's a new dimension to things, Roy <g>. It's much tougher now, since
there would probably be multiple returns for the month/year specified in AF6
(and I'm sure you want all of these).

Here's a play with non-array formulas which can drive out the results you
seek. I've provided 2 criteria options to illustrate depending on how
exactly you might wish to frame it up.

A sample implementation available at:
http://cjoint.com/?ckeMtEcoFC
Extract from 2 cols based on Mth_Yr Criteria.xls

In sheet: 1

Option1: Extract lines if date open & date closed are within the "mm/yyyy"
input in AF6

Data assumed within row 8 to row 20
Pre-format AF6 as Text
Inputs will be entered in AF6 as : mm/yyyy,
eg: 02/2006 (for Feb 2006)

In AF8:
=IF(ISERROR(SMALL($AG$8:$AG$20,ROW(A1))),"",
INDEX(TRIM($K$8:$K$20&"
"&$O$8:$O$20),MATCH(SMALL($AG$8:$AG$20,ROW(A1)),$AG$8:$AG$20,0)))

In AG8:
=IF(AND(TEXT(J8,"mm/yyyy")=$AF$6,TEXT(T8,"mm/yyyy")=$AF$6),ROW()/10^10,"")

Select AF8:AG8, copy down to AG20
(AG8:AG20 is the criteria col, with an arbitrary tiebreaker built-in)

-----------
In sheet: 2,

Option 2: Extract lines if date open is within the "mm/yyyy" input in AF6,
and if there is a date closed (i.e., item's closed), irrespective of date
closed falling outside the mth/yr indicated in AF6

Data assumed within row 8 to row 20
Pre-format AF6 as Text
Inputs will be entered in AF6 as : mm/yyyy,
eg: 02/2006 (for Feb 2006)

In AF8 (no change, same as in sheet: 1):
=IF(ISERROR(SMALL($AG$8:$AG$20,ROW(A1))),"",
INDEX(TRIM($K$8:$K$20&"
"&$O$8:$O$20),MATCH(SMALL($AG$8:$AG$20,ROW(A1)),$AG$8:$AG$20,0)))

In AG8 (changed):
=IF(AND(TEXT(J8,"mm/yyyy")=$AF$6,TRIM(T8)<>""),ROW()/10^10,"")

Select AF8:AG8, copy down to AG20
(AG8:AG20 is the criteria col, with an arbitrary tiebreaker built-in)
 
Top