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.

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.



roy.okinawa wrote:
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.

Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:

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.



roy.okinawa wrote:
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.

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.


roy.okinawa wrote:

Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:


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.



roy.okinawa wrote:

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.

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



"Govind" wrote:

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.


roy.okinawa wrote:

Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:


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.



roy.okinawa wrote:

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.

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.



roy.okinawa wrote:

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



"Govind" wrote:


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.


roy.okinawa wrote:


Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:



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.



roy.okinawa wrote:


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.

Govind,

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

Thanks,

Roy

"Govind" wrote:

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.



roy.okinawa wrote:

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



"Govind" wrote:


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.


roy.okinawa wrote:


Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:



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.



roy.okinawa wrote:


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.

Hi,

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

Regards

Govind.


roy.okinawa wrote:
Govind,

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

Thanks,

Roy

"Govind" wrote:


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.



roy.okinawa wrote:


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



"Govind" wrote:



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.


roy.okinawa wrote:



Govind,

The date will never be the same in column J and T.

Roy

"Govind" wrote:




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.



roy.okinawa wrote:



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.

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(TR IM($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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


"Max" wrote:

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(TR IM($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"roy.okinawa" wrote:
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.

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)),$A G$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)),$A G$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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The "lines" extracted in this case refers to the concat text from cols K and
O
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---