software

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:
gt; Here is what I have:
gt;
gt; Column J is the date opened. Column T is the date closed. Of course,
gt; column T will have no date if still open.
gt; Column K and O may or may not have text.
gt; Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt; search.
gt;
gt; What I need is a formula in AF8 that will look at the date entered in AF6
gt; and find a match in columns J and T. Then it will look to see if there is
gt; text in K and O. If so, populate AF8 with that text.
gt;

Govind,

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

Roy

quot;Govindquot; wrote:

gt; Hi,
gt;
gt; What if the same date is there in Column J and Column T? How do you
gt; want your formula to work in that case?
gt;
gt; Regards
gt;
gt; Govind.
gt;
gt;
gt;
gt; roy.okinawa wrote:
gt; gt; Here is what I have:
gt; gt;
gt; gt; Column J is the date opened. Column T is the date closed. Of course,
gt; gt; column T will have no date if still open.
gt; gt; Column K and O may or may not have text.
gt; gt; Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt; gt; search.
gt; gt;
gt; gt; What I need is a formula in AF8 that will look at the date entered in AF6
gt; gt; and find a match in columns J and T. Then it will look to see if there is
gt; gt; text in K and O. If so, populate AF8 with that text.
gt; gt;
gt;

Hi Roy,

Try this formula

=OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6))-1,2)amp;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:

gt; Govind,
gt;
gt; The date will never be the same in column J and T.
gt;
gt; Roy
gt;
gt; quot;Govindquot; wrote:
gt;
gt;
gt;gt;Hi,
gt;gt;
gt;gt;What if the same date is there in Column J and Column T? How do you
gt;gt;want your formula to work in that case?
gt;gt;
gt;gt;Regards
gt;gt;
gt;gt;Govind.
gt;gt;
gt;gt;
gt;gt;
gt;gt;roy.okinawa wrote:
gt;gt;
gt;gt;gt;Here is what I have:
gt;gt;gt;
gt;gt;gt;Column J is the date opened. Column T is the date closed. Of course,
gt;gt;gt;column T will have no date if still open.
gt;gt;gt;Column K and O may or may not have text.
gt;gt;gt;Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt;gt;gt;search.
gt;gt;gt;
gt;gt;gt;What I need is a formula in AF8 that will look at the date entered in AF6
gt;gt;gt;and find a match in columns J and T. Then it will look to see if there is
gt;gt;gt;text in K and O. If so, populate AF8 with that text.
gt;gt;gt;
gt;gt;

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
quot;Govindquot; wrote:

gt; Hi Roy,
gt;
gt; Try this formula
gt;
gt; =OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6))-1,2)amp;OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,3)
gt;
gt; entered with CTRL SHIFT ENTER.
gt;
gt; Here J1:J100 is the range with date opened and T1:T100 is the range with
gt; date closed. Change the formula to suit your actual range.
gt;
gt; Regards
gt;
gt; Govind.
gt;
gt;
gt; roy.okinawa wrote:
gt;
gt; gt; Govind,
gt; gt;
gt; gt; The date will never be the same in column J and T.
gt; gt;
gt; gt; Roy
gt; gt;
gt; gt; quot;Govindquot; wrote:
gt; gt;
gt; gt;
gt; gt;gt;Hi,
gt; gt;gt;
gt; gt;gt;What if the same date is there in Column J and Column T? How do you
gt; gt;gt;want your formula to work in that case?
gt; gt;gt;
gt; gt;gt;Regards
gt; gt;gt;
gt; gt;gt;Govind.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;roy.okinawa wrote:
gt; gt;gt;
gt; gt;gt;gt;Here is what I have:
gt; gt;gt;gt;
gt; gt;gt;gt;Column J is the date opened. Column T is the date closed. Of course,
gt; gt;gt;gt;column T will have no date if still open.
gt; gt;gt;gt;Column K and O may or may not have text.
gt; gt;gt;gt;Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt; gt;gt;gt;search.
gt; gt;gt;gt;
gt; gt;gt;gt;What I need is a formula in AF8 that will look at the date entered in AF6
gt; gt;gt;gt;and find a match in columns J and T. Then it will look to see if there is
gt; gt;gt;gt;text in K and O. If so, populate AF8 with that text.
gt; gt;gt;gt;
gt; gt;gt;
gt;

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)amp;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:

gt; I don't see any reference to Columns K or O in the formula. Is this formula
gt; going to search for the text in those columns?
gt;
gt; Roy
gt;
gt;
gt;
gt; quot;Govindquot; wrote:
gt;
gt;
gt;gt;Hi Roy,
gt;gt;
gt;gt;Try this formula
gt;gt;
gt;gt;=OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,2)amp;OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,3)
gt;gt;
gt;gt;entered with CTRL SHIFT ENTER.
gt;gt;
gt;gt;Here J1:J100 is the range with date opened and T1:T100 is the range with
gt;gt;date closed. Change the formula to suit your actual range.
gt;gt;
gt;gt;Regards
gt;gt;
gt;gt;Govind.
gt;gt;
gt;gt;
gt;gt;roy.okinawa wrote:
gt;gt;
gt;gt;
gt;gt;gt;Govind,
gt;gt;gt;
gt;gt;gt;The date will never be the same in column J and T.
gt;gt;gt;
gt;gt;gt;Roy
gt;gt;gt;
gt;gt;gt;quot;Govindquot; wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;Hi,
gt;gt;gt;gt;
gt;gt;gt;gt;What if the same date is there in Column J and Column T? How do you
gt;gt;gt;gt;want your formula to work in that case?
gt;gt;gt;gt;
gt;gt;gt;gt;Regards
gt;gt;gt;gt;
gt;gt;gt;gt;Govind.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;roy.okinawa wrote:
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;gt;Here is what I have:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Column J is the date opened. Column T is the date closed. Of course,
gt;gt;gt;gt;gt;column T will have no date if still open.
gt;gt;gt;gt;gt;Column K and O may or may not have text.
gt;gt;gt;gt;gt;Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt;gt;gt;gt;gt;search.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;What I need is a formula in AF8 that will look at the date entered in AF6
gt;gt;gt;gt;gt;and find a match in columns J and T. Then it will look to see if there is
gt;gt;gt;gt;gt;text in K and O. If so, populate AF8 with that text.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;

Govind,

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

Thanks,

Roy

quot;Govindquot; wrote:

gt; Hi,
gt;
gt; Its not explicitly mentioned but are worked out by the formula. But my
gt; reference wasnt correct in my earlier formula and hence use this
gt;
gt; =OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6))-1,1)amp;OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,5)
gt;
gt; The offset formula starts in column J, matches the given date, goes that
gt; row and then moves 1 column right to refer to column K in the first
gt; formula. In the second one, it moves 5 column right to refer to column
gt; O. Change J1:J100 and T1:T100 to the actual range.
gt;
gt;
gt;
gt; Regards
gt;
gt; Govind.
gt;
gt;
gt;
gt; roy.okinawa wrote:
gt;
gt; gt; I don't see any reference to Columns K or O in the formula. Is this formula
gt; gt; going to search for the text in those columns?
gt; gt;
gt; gt; Roy
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Govindquot; wrote:
gt; gt;
gt; gt;
gt; gt;gt;Hi Roy,
gt; gt;gt;
gt; gt;gt;Try this formula
gt; gt;gt;
gt; gt;gt;=OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,2)amp;OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,3)
gt; gt;gt;
gt; gt;gt;entered with CTRL SHIFT ENTER.
gt; gt;gt;
gt; gt;gt;Here J1:J100 is the range with date opened and T1:T100 is the range with
gt; gt;gt;date closed. Change the formula to suit your actual range.
gt; gt;gt;
gt; gt;gt;Regards
gt; gt;gt;
gt; gt;gt;Govind.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;roy.okinawa wrote:
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;gt;Govind,
gt; gt;gt;gt;
gt; gt;gt;gt;The date will never be the same in column J and T.
gt; gt;gt;gt;
gt; gt;gt;gt;Roy
gt; gt;gt;gt;
gt; gt;gt;gt;quot;Govindquot; wrote:
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt;gt;Hi,
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;What if the same date is there in Column J and Column T? How do you
gt; gt;gt;gt;gt;want your formula to work in that case?
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;Regards
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;Govind.
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;roy.okinawa wrote:
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Here is what I have:
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;Column J is the date opened. Column T is the date closed. Of course,
gt; gt;gt;gt;gt;gt;column T will have no date if still open.
gt; gt;gt;gt;gt;gt;Column K and O may or may not have text.
gt; gt;gt;gt;gt;gt;Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt; gt;gt;gt;gt;gt;search.
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;gt;What I need is a formula in AF8 that will look at the date entered in AF6
gt; gt;gt;gt;gt;gt;and find a match in columns J and T. Then it will look to see if there is
gt; gt;gt;gt;gt;gt;text in K and O. If so, populate AF8 with that text.
gt; gt;gt;gt;gt;gt;
gt; gt;gt;gt;gt;
gt;

Hi,

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

Regards

Govind.roy.okinawa wrote:
gt; Govind,
gt;
gt; It is not working. The text in columns K and O are not being brought
gt; across.
gt;
gt; Thanks,
gt;
gt; Roy
gt;
gt; quot;Govindquot; wrote:
gt;
gt;
gt;gt;Hi,
gt;gt;
gt;gt;Its not explicitly mentioned but are worked out by the formula. But my
gt;gt;reference wasnt correct in my earlier formula and hence use this
gt;gt;
gt;gt;=OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,1)amp;OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,5)
gt;gt;
gt;gt;The offset formula starts in column J, matches the given date, goes that
gt;gt;row and then moves 1 column right to refer to column K in the first
gt;gt;formula. In the second one, it moves 5 column right to refer to column
gt;gt;O. Change J1:J100 and T1:T100 to the actual range.
gt;gt;
gt;gt;
gt;gt;
gt;gt;Regards
gt;gt;
gt;gt;Govind.
gt;gt;
gt;gt;
gt;gt;
gt;gt;roy.okinawa wrote:
gt;gt;
gt;gt;
gt;gt;gt;I don't see any reference to Columns K or O in the formula. Is this formula
gt;gt;gt;going to search for the text in those columns?
gt;gt;gt;
gt;gt;gt;Roy
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;quot;Govindquot; wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;Hi Roy,
gt;gt;gt;gt;
gt;gt;gt;gt;Try this formula
gt;gt;gt;gt;
gt;gt;gt;gt;=OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,2)amp;OFFSET(J1,MATCH(1,(J1:J100=AF6) (T1:T100=AF6) )-1,3)
gt;gt;gt;gt;
gt;gt;gt;gt;entered with CTRL SHIFT ENTER.
gt;gt;gt;gt;
gt;gt;gt;gt;Here J1:J100 is the range with date opened and T1:T100 is the range with
gt;gt;gt;gt;date closed. Change the formula to suit your actual range.
gt;gt;gt;gt;
gt;gt;gt;gt;Regards
gt;gt;gt;gt;
gt;gt;gt;gt;Govind.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;roy.okinawa wrote:
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;gt;Govind,
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;The date will never be the same in column J and T.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;Roy
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;quot;Govindquot; wrote:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Hi,
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;What if the same date is there in Column J and Column T? How do you
gt;gt;gt;gt;gt;gt;want your formula to work in that case?
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Regards
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;Govind.
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;roy.okinawa wrote:
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;Here is what I have:
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;Column J is the date opened. Column T is the date closed. Of course,
gt;gt;gt;gt;gt;gt;gt;column T will have no date if still open.
gt;gt;gt;gt;gt;gt;gt;Column K and O may or may not have text.
gt;gt;gt;gt;gt;gt;gt;Column AF6 has a date, mm/dd/yyyy, that is entered/changed depending on the
gt;gt;gt;gt;gt;gt;gt;search.
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt;What I need is a formula in AF8 that will look at the date entered in AF6
gt;gt;gt;gt;gt;gt;gt;and find a match in columns J and T. Then it will look to see if there is
gt;gt;gt;gt;gt;gt;gt;text in K and O. If so, populate AF8 with that text.
gt;gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;

Perhaps try also, in AF8 (normal ENTER):

=IF(AF6=quot;quot;,quot;quot;,IF(ISNA(MATCH(AF6,$J$8:$J$100,0)),IF (ISNA(MATCH(AF6,$T$8:$T$10
0,0)),quot;No date matches foundquot;,INDEX(TRIM($K$8:$K$100amp;quot;
quot;amp;$O$8:$O$100),MATCH(AF6,$T$8:$T$100,0))),INDEX(TR IM($K$8:$K$100amp;quot;
quot;amp;$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: quot;No date matches foundquot;

Adapt to suit ..

A sample construct is at:
cjoint.com/?bFrwPxPItm
roy_okinawa_TextSearch_wks.xls
--
Max
Singapore
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.

Royquot;Maxquot; wrote:

gt; Perhaps try also, in AF8 (normal ENTER):
gt;
gt; =IF(AF6=quot;quot;,quot;quot;,IF(ISNA(MATCH(AF6,$J$8:$J$100,0)),IF (ISNA(MATCH(AF6,$T$8:$T$10
gt; 0,0)),quot;No date matches foundquot;,INDEX(TRIM($K$8:$K$100amp;quot;
gt; quot;amp;$O$8:$O$100),MATCH(AF6,$T$8:$T$100,0))),INDEX(TR IM($K$8:$K$100amp;quot;
gt; quot;amp;$O$8:$O$100),MATCH(AF6,$J$8:$J$100,0))))
gt;
gt; Some assumptions:
gt; ---------------------------
gt; Source range in cols J, K, O, T is within row 8 to row 100
gt; Unique dates in cols J and T
gt; Sequential checks: Col J is checked ahead of col T
gt; Returns of text is required from both cols K and O, separated by a space
gt; Any unmatched dates will return: quot;No date matches foundquot;
gt;
gt; Adapt to suit ..
gt;
gt; A sample construct is at:
gt; cjoint.com/?bFrwPxPItm
gt; roy_okinawa_TextSearch_wks.xls
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;
gt;