• Status: Solved
• Priority: Medium
• Security: Public
• Views: 483

# Excel:Find MAX of Last 3 Non-Empty Rows of Sub-Range

Column A contains company names and column B contains sales data. How do I find the MAX value of the last three non-empty rows per company?

Best wishes,

John
0
jfdinneen
• 5
• 4
• 2
• +1
2 Solutions

Commented:
Does this have to be a formula or VBA?
Also, can we assume it looks something like this?

Company, Sales
A,12.23
B,51.22
B,11.22 *
B,
B,1.22 *
B,9.87 *
C,1.23

Where the * rows are the last 3 non-empty for B
Note also the assumption that the company records are contiguous.
0

Microsoft MVP ExcelCommented:
Hello jfdinneen,

a data sample would be helpful. "last three non-empty rows per company?" seems to imply that there are several rows per company. Is that correct?

cheers, teylyn
0

Author Commented:
cyberwiki / teylyn,

Thanks for the prompt replies.
The data sample cyberwiki outlined above looks correct.

Best wishes,

John
0

Author Commented:
cyberwiki / teylyn,

The solution must be a function but either formula of VBA is acceptable.

Best wishes,

John
0

Commented:
A single function/formula won't be able to give you
"MAX value of the last three non-empty rows per company?"
can it?
Unless you mean one single value overall, from the last 3 sales per company.
So in the sample I gave above, it would return a single value "12.23" from A, since the largest value "51.22" from B is out of consideration.

I have also assumed that "last three" refers to row order, and there are no other date/id/time columns.

0

Author Commented:
cyberwiki,

As per your example, the function should return 12.23, 11.22, and 1.23 for A, B, and C respectively.

Best wishes,

John
0

Commented:
So how would you use this function... I imagine one formula per cell, per company?
Like
H2: =MaxFromLast3(A:B, "A")
H3: =MaxFromLast3(A:B, "B")
etc

But that would require each company be manually inserted into the formula.
"The solution must be a function but either formula of VBA is acceptable."
I assumed by the bold part that you mean a function that can be used in the formula bar, but those won't return results into multiple cells (one per company).

Such a function for MaxFromLast3 (range, company as string) is certainly possible, where range is implied to be 2 columns (any number of rows), company on the left.

If you are after a Macro that can be run to fill some area of a sheet with the data, automatically putting one row out per company
company,max
that is also possible.

0

Author Commented:
cyberwiki,

Ideally, the function MaxFromLast3 would work as follows (array formula):
={ If(A1:A100=A1,MaxFromLast3(B1:B100)) }
={ If(A1:A100=A2,MaxFromLast3(B1:B100)) }
...

Company, Sales, Max (Last 3)
A,12.23, 12.33
B,51.22
B,11.22 *
B,
B,1.22 *
B,9.87 *,11.22
C,1.23,1.23

Best wishes,

John
0

Commented:
You could use this array formula

=MAX(IF(ROW(B1:B100)>=LARGE(IF(A1:A100=A1,IF(B1:B100<>"",ROW(B1:B100))),3),IF(A1:A100=A1,B1:B100)))

confirmed with CTRL+SHIFT+ENTER

That assumes that there will be at least 3 entries where the criteria match, if not then change to

=MAX(IF(ROW(B1:B100)>=LARGE(IF(A1:A100=A1,IF(B1:B100<>"",ROW(B1:B100))),MIN(3,SUM((A1:A100=A1)*(B1:B100<>"")))),IF(A1:A100=A1,B1:B100)))

regards, barry
0

Commented:
See example attached with some random values (press F9 to re-generate)

Formulas in C2:C6 should give the same results as D2:D6.......unless there are fewer than 3 qualifying rows....in which case column C will have an error

regards, barry
26642825.xls
0

Author Commented:
Thanks Barry for the ideal solution.

Best wishes,

John
0

Commented:
Hi John,

Please find attached a sheet with the function below, and an example of using it.
It returns an array of 2 values:
1- the value
2- the row on which it was found (not relative terms like Match, just absolute)

The formula in C uses the 2nd value returned to inspect whether it is the max row, and only then shows the value from B (no need to run the function again).

If the function were to return [only] the max value [without the row reference], then a more complicated formula on the Excel side would be needed to tiebreak between multiple rows with the same value.

Hope this helps

Regards
``````Function MaxLast3(ByRef R As Range, ByVal company As String)
If IsEmpty(R) Then
Func MaxLast3(ByRef R As Range, ByVal company As String)
If IsEmpty(R) Then
Exit Function
End If
Set R = Intersect(R, R.Worksheet.UsedRange)
If R Is Nothing Then
Exit Function
End If

Dim which As Integer, test As Range, where As Range, maxVal
which = 1
maxVal = ""
Set where = Nothing
Set test = R.Cells(1)

Set test = R.Columns(1).Find(What:=company, After:=test, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
While Not test Is Nothing
If test.Offset(, 1) > "" Then
If which = 1 Or Val(test.Offset(, 1)) > maxVal Then
Set where = test.Offset(, 1)
maxVal = Val(test.Offset(, 1))
End If
which = which + 1
End If
If which <= 3 And Not where Is Nothing Then
Set test = R.Columns(1).Find(What:=company, After:=test, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
Else
Set test
``````
Book1.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.