Getting The MAX value of Column Only Of Those Rows Holding A Certain Value

[Jenn68]

What is an efficient VBA solution to doing this?

I have a worksheet with values in row A, B and C.
Variable recordic = 31286

This value can be found anywhere from once to hundreds in column A.
Column B holds numbers.

I need code that will find the largest value from column B (max) for the values in A that equal recordid.

In other words, if I were to filter column A for the value of recordid, I would need to find the largest number in column B of the filtered data. If I filter the data, and apply max to column B of the filtered data, I don't get the right answer.

[Keebellah]

I would suggest using an extra (helper) column maybe next to column B,
The header a field where you enter the Id you want to check and in the rows below a formula that shows the value in B when the ID is = to the ID in the header and then show the MAX value of this number.
You have not mentioned it but I am assuming you're working on a MAC (version 2019 does not exist for Windows)
Attach a sample file even if on a Windows system most things work seamlessly with a MAC

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Jenn68]

Thank you Hans for your sharing your approach.
I actually played around and found this solution. It might be a bit slow with vast amounts of data, but it seems to be doing the trick.

Please Login or Register  to view this content.

And no, not a MAC unfortunately. Perhaps Office 2019 is also known as Office 365?

[jindon]

Variable "rn" should be declared as Variant.

Please Login or Register  to view this content.

[Jenn68]

Thank you jindon. That cleaned things up a bait.