VBA Max based on Criteria

[Mr TfL]

Hi Everyone, I've been using the site for a while but this is my first post, so if there any faux pas' please let me know and I'll make sure it doesn't happen again.

Ok, so on to the scenario... I've used VBA to manipulate data for different teams displaying all of their individual open cases, the corresponding reference number, date the case was received and how many days old it is, amongst other things. Here's a small sample of the data (approx 15,000 unsorted records in total):

<TABLE style="WIDTH: 498pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=663 border=0><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 4266" width=150><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 2816" width=99><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 2958" width=104><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 1991" width=70><COL style="WIDTH: 60pt" span=3 width=80><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 113pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #c6c4c4" width=150 height=21>Team</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae; WIDTH: 74pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=99>Received On</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae; WIDTH: 78pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=104>Reference No.</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae; WIDTH: 53pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=70>Transactions</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=80>Days Open</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=80>In SLA</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=80>Out of SLA</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 113pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #c6c4c4" width=150 height=21>Team 1</TD><TD class=xl66 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 74pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=99>02.04.2010</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 78pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=104>0000123456</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 53pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=70>1</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>102</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80></TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>Out of SLA</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 113pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #c6c4c4" width=150 height=21>Team 2</TD><TD class=xl66 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 74pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=99>27.04.2010</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 78pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=104>0000456789</TD><TD class=xl68 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 53pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=70>1</TD><TD class=xl68 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=80>77</TD><TD class=xl68 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=80></TD><TD class=xl68 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=80>Out of SLA</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 113pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #c6c4c4" width=150 height=21>Team 3</TD><TD class=xl66 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 74pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=99>10.05.2010</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 78pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=104>0000987654</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 53pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=70>1</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>64</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80></TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>Out of SLA</TD></TR></TBODY></TABLE>

What I'd now like to do, is create a summary to the right of the table listing the seven teams, the oldest 'Received On' date for each team, the number of days that the oldest case has been open, the associated reference no. and whether or not the case is in SLA. Example below:

<TABLE style="WIDTH: 339pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=452 border=0><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 3754" width=132><COL style="WIDTH: 60pt" span=3 width=80><TBODY><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD class=xl68 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae 1pt solid; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 339pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 17.25pt; BACKGROUND-COLOR: #c6c4c4" width=452 colSpan=5 height=23>Oldest Case Summary

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #c6c4c4" width=80 height=21>Team</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 99pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=132>

Oldest Case Received On​

</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=80>Days Open</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=80>SLA Status</TD><TD class=xl65 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #c6c4c4" width=80>Ref No.</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl71 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=80 height=21>Team 1</TD><TD class=xl66 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 99pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=132>02.04.2010</TD><TD class=xl66 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>102</TD><TD class=xl66 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>Out of SLA</TD><TD class=xl72 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: white" width=80>0000123456</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl74 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae 1pt solid; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #e9eef4" width=80 height=21>Team 2</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 99pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=132>27.04.2010</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=80>77</TD><TD class=xl67 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=80>Out of SLA</TD><TD class=xl73 style="BORDER-RIGHT: #aeaeae 1pt solid; BORDER-TOP: #aeaeae; BORDER-LEFT: #aeaeae; WIDTH: 60pt; BORDER-BOTTOM: #aeaeae 1pt solid; BACKGROUND-COLOR: #e9eef4" width=80>0000456789</TD></TR></TBODY></TABLE>​

I've seen methods for getting the data in the worksheet as an array formula, but the report is generated on a new worksheet from another system and needs to be prepared by different members of my team with various skill levels in Excel, so I'd rather do it as a macro that I can distribute with little hassle.

Please help! (again sorry for any faux pas')

 

[Andrew Poulsom]

Welcome to the Board.

The macro recorder will give you a good start. Post back if you are still stuck on any aspect.

 

[Mr TfL]

Hi Andrew, thanks for the advice.

I've spent some time with the macro recorder and I can get the max value to display without any issues. I'm not sure how I'd get the rest of the corresponding data to display from the row though. What method would you recommend?

I'm sure it's something extremely simple, but I can't get it to run at the moment.

Thinking: I wonder... would a .Select, R1C1 combo work? If it does, how do I go about implementing it?

Sorry for the noob question, but I'm not very good with VBA yet.

 

[Andrew Poulsom]

I thought you said you knew how to get the data with array formulas. That's what I would use, possibly converting to values when done.

Alternatively, once you know the row number of the cell containing the appropriate max value it should be easy to get the data from the other columns.

If you need more help post the code you have so far.