If And Match With a MAX and Offset

[hansjhamm]

I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans

 

[dolivastro]

I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom

 

[hansjhamm]

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom

I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans

 

[dolivastro]

Hi Hans: I have 2 suggestions, but you may not like either of them.

1. Move the whole thing over to Access. What you want is more in the
nature of a database than a spreadsheet.

2. Keep it in Excel, but write a VBA function that does exactly what
you want, instead of using a worksheet function.

I can help with the function, but you need to know your way about the
VB Editor.

Dom

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom

I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans

 

[hansjhamm]

Dom,
Typically I use formulas to work the data and once I can confirm that I
am getting the correct data...I start converting to VBA...I know it is
the LONG way around, but still fairly new to the VBA game...So any help
you could give me will be appreciated!

Hey, if you can help me to get it to work I will drink a beer and say a
toast to you!

Hans

Hi Hans: I have 2 suggestions, but you may not like either of them.

1. Move the whole thing over to Access. What you want is more in the
nature of a database than a spreadsheet.

2. Keep it in Excel, but write a VBA function that does exactly what
you want, instead of using a worksheet function.

I can help with the function, but you need to know your way about the
VB Editor.

Dom

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom




(e-mail address removed) wrote:
I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans

 

[dolivastro]

Here it is: It seems to work on this side, anyway.

The call is, for example, =MyFunc(C9,Sheet2!$D$11:$J$18). What the
arguments refer to should be obvious. You put the formula in the cell
you want the return.

--------------------------------------------------
Public Function MyFunc(StoreID As Variant, r As Range) As Variant

Const p_StoreCol As Long = 1
Const p_DateCol As Long = 5
Const p_ReturnCol As Long = 7

Dim FRow As Long
Dim LRow As Long

Dim i As Long
Dim T_Return As Variant
Dim T_Date As Date
Dim T_Caught As Boolean


FRow = 1
LRow = r.Rows.Count
T_Caught = False

For i = FRow To LRow
If (r.Cells(i, p_StoreCol).Value = StoreID) Then
If (r.Cells(i, p_DateCol).Value > T_Date) Then
T_Caught = True
T_Date = r.Cells(i, p_DateCol).Value
T_Return = r.Cells(i, p_ReturnCol).Value
End If
End If
Next i


MyFunc = "n/a"
If (T_Caught) Then MyFunc = T_Return

End Function
---------------------------------------------------------


Hope this helps,
Dom

Dom,
Typically I use formulas to work the data and once I can confirm that I
am getting the correct data...I start converting to VBA...I know it is
the LONG way around, but still fairly new to the VBA game...So any help
you could give me will be appreciated!

Hey, if you can help me to get it to work I will drink a beer and say a
toast to you!

Hans

Hi Hans: I have 2 suggestions, but you may not like either of them.

1. Move the whole thing over to Access. What you want is more in the
nature of a database than a spreadsheet.

2. Keep it in Excel, but write a VBA function that does exactly what
you want, instead of using a worksheet function.

I can help with the function, but you need to know your way about the
VB Editor.

Dom

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

(e-mail address removed) wrote:
I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom




(e-mail address removed) wrote:
I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans

 

[Guest]

Hans,

Here's a solution for you (not the most elegant, but it works):

=SUM(IF(Sheet2!$H$3:$H$5000=MAX(IF(C3=Sheet2!$D$3:$D$5000,Sheet2!$H$3:$H$5000)),IF(C3=Sheet2!$D$3:$D$5000,Sheet2!$JI$3:$J$5000)))

You have to include references to rows - in my example it's 3 and 5000. This
is an array formula, so you must press CTRL+SHIFT+ENTER to add braces {}
around it.
If you have multiple entries for the same store for the same Max date, they
will be added. To select Min or Max from the multiple entries, change SUM
accordingly; don't forget CTRL+SHIFT+ENTER.

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom

I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans

 

[hansjhamm]

Hey Guys,

Thanks for the help...long time in responding, got pulled into another
project all of a sudden and did not get back to this for a few days.
Anyway, I will give a shout back once I can and ket you know how it
turned out...

Again Thanks!!!

Hans

Hans,

Here's a solution for you (not the most elegant, but it works):

=SUM(IF(Sheet2!$H$3:$H$5000=MAX(IF(C3=Sheet2!$D$3:$D$5000,Sheet2!$H$3:$H$5000)),IF(C3=Sheet2!$D$3:$D$5000,Sheet2!$JI$3:$J$5000)))

You have to include references to rows - in my example it's 3 and 5000. This
is an array formula, so you must press CTRL+SHIFT+ENTER to add braces {}
around it.
If you have multiple entries for the same store for the same Max date, they
will be added. To select Min or Max from the multiple entries, change SUM
accordingly; don't forget CTRL+SHIFT+ENTER.

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom




(e-mail address removed) wrote:
I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans