Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Can someone help me out with this conundrum and prvide me with some appropriate VBA code:

in column 'C' I have a number and in column 'O' I have a date.

203126662 10/05/2013

203126662 16/05/2013

203126662 17/05/2013

203126662 17/05/2013

However, as in the example above there can be multiple numbers the same in cells 'C' but the dates are different in cells 'O'

What I need to do is put the most recent date in column 'AB' where the numbers in column 'C' are the same so it would look like this

203126662 10/05/2013 17/05/2013

203126662 16/05/2013 17/05/2013

203126662 17/05/2013 17/05/2013

203126662 17/05/2013 17/05/2013

Thanks

in column 'C' I have a number and in column 'O' I have a date.

203126662 10/05/2013

203126662 16/05/2013

203126662 17/05/2013

203126662 17/05/2013

However, as in the example above there can be multiple numbers the same in cells 'C' but the dates are different in cells 'O'

What I need to do is put the most recent date in column 'AB' where the numbers in column 'C' are the same so it would look like this

203126662 10/05/2013 17/05/2013

203126662 16/05/2013 17/05/2013

203126662 17/05/2013 17/05/2013

203126662 17/05/2013 17/05/2013

Thanks

@max(xx:xx) will do it but replicating for each group is out of my scope

q-28138022.xlsx

q-28138022.xlsx

Max will only work if column C stays the same. Use below array formula to capture any variations.

{=MAX(IF($C$2:$C$5=C2,$O$2

Change it to C1 if you don't use headers.

(to make it array formula press CTRL+SHIFT+ENTER)

Max-Array.xls

```
Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C:C"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("AB:AB") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("O:O") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A:AB")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
```

The Macro 'Macro1' removes the header in row 1

is this because I am using 2010?

bizzare

Book3.xlsx

In order to make it work the ranges cannot be full columns.

I have added 2 dynamic named ranges "Ref" and "Entry_Date" this will always bring back correct number of rows.

Please see the attached file for details. Let me know if you need any explanation.

Book3.xlsx

=MaxIf(A:A,A2,B:B) 'This is a regular formula

```
Function MaxIf(rgToCheck As Range, Criteria As Variant, rgToMax As Range) As Variant
'Function tests rgToCheck for Criteria. Returns maximum of corresponding cells in rgToMax when rgToCheck cell satisfies Criteria.
'rgToCheck is range to be tested against Criteria
'Criteria may be a single value or an array of values
'rgToMax is range from which the maximum value is selected
'Function returns the maximum value if Criteria is a single value. It returns an array of maximums if Criteria is an array. _
=MaxIf(A1:A7, ">0",B1:B7) returns a single value _
=MaxIf(A1:A7, {">5", "<2"},B1:B7) returns a two column array of values _
=MaxIf(A1:A7, {">5"; "<2"},B1:B7) returns a two row array of values (semicolon instead of comma in array constant) _
=MaxIf(A1:A7, {">5", ">20"; "<2", "<1"}},B1:B7) returns a two column by two row array of values
MaxIf = MinMaxIf(rgToCheck, Criteria, rgToMax, False)
End Function
Private Function MinMaxIf(rgToCheck As Range, Criteria As Variant, rgToMinMax As Range, bMinimum As Boolean) As Variant
'Function tests rgToCheck for Criteria. Returns minimum/maximum of corresponding cells in rgToMinMax when rgToCheck cell satisfies Criteria.
'rgToCheck is range to be tested against Criteria
'Criteria may be a single value or an array of values
'rgToMinMax is range from which the minimum/maximum value is selected
'bMinimum is True when function returns a minimum value. It is False if a maximum value is returned.
'Function returns the minimum/maximum value if Criteria is a single value. It returns an array of minimums/maximums if Criteria is an array.
Dim i As Long, ii As Long, j As Long, jj As Long, n As Long, nCols As Long, nRows As Long, nnCols As Long, nnRows As Long
Dim d As Double
Dim iFirstCheck As Integer, iLastCheck As Integer, k As Integer
Dim vCheck As Variant, vCriteria As Variant, vResults As Variant
Dim wbCheck As Workbook
Set rgToCheck = Intersect(rgToCheck, rgToCheck.Worksheet.UsedRange) 'Avoid excessive runtime if needlessly passed entire row or column
nnCols = rgToCheck.Columns.Count
nnRows = rgToCheck.Rows.Count
If nnCols > rgToMinMax.Columns.Count Then
MinMaxIf = "#ColumnMatch"
Exit Function
ElseIf nnRows > rgToMinMax.Rows.Count Then
MinMaxIf = "#RowMatch"
Exit Function
End If
'Determine whether Criteria is an array. If so, establish its dimensions.
On Error Resume Next
If VarType(Criteria) >= vbArray Then
nCols = UBound(Criteria)
nRows = UBound(Criteria, 2)
If nCols = 0 Then nCols = 1
If nRows = 0 Then nRows = 1
ReDim vResults(1 To nRows, 1 To nCols)
Else
'ReDim vResults(1 To 1, 1 To 1)
n = 1
nRows = 1
nCols = 1
End If
n = nRows * nCols
On Error GoTo 0
For i = 1 To nRows
For j = 1 To nCols
d = IIf(bMinimum, 1E+308, -1E+308) 'Starting value
If n = 1 Then
vCriteria = Criteria
Else
If nCols = 1 Then
vCriteria = Criteria(i)
ElseIf nRows = 1 Then
vCriteria = Criteria(j)
Else
vCriteria = Criteria(i, j)
End If
End If
For ii = 1 To nnRows
For jj = 1 To nnCols
If (Application.CountIf(rgToCheck.Cells(ii, jj), vCriteria) = 1) And (rgToMinMax.Cells(ii, jj).Value <> "") Then
If bMinimum Then
d = Application.Min(d, rgToMinMax.Cells(ii, jj).Value)
Else
d = Application.Max(d, rgToMinMax.Cells(ii, jj).Value)
End If
End If
Next
Next
If n = 1 Then
vResults = IIf(Abs(d) = 1E+308, "#None", d)
Else
vResults(i, j) = IIf(Abs(d) = 1E+308, "#None", d)
End If
Next
Next
MinMaxIf = vResults
End Function
```

BradMaxIfQ28138002.xlsm

I opened the file you attached but it is not doing what my original request was.

"I need to put the most recent date in column 'AB' where the numbers in column 'C' are the same"

Below is taken from the spreadsheet attached to your reply.

The result on the below should be 25/2/2013 not 18/07/2011

Reference Entry Date Date Age

148311118 18/07/2011 18/07/2011

148311118 25/02/2013 18/07/2011

The result on the below should be 10/5/2013 not 17/05/2013

Reference Entry Date Date Age

903017819 10/05/2013 17/05/2013

903017819 10/05/2013 17/05/2013

903017819 03/05/2013 17/05/2013

903017819 10/05/2013 17/05/2013

903017819 10/05/2013 17/05/2013

903017819 10/05/2013 17/05/2013

903017819 03/05/2013 17/05/2013

903017819 10/05/2013 17/05/2013

Is it possible?

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.

All Courses

From novice to tech pro — start learning today.

In your earlier Comment http://www.experts-exchange.com/Q_28138002.html#a39194533 you complained: With Excel 2010, you can array-enter formulas that refer to entire columns.

Your problem was that you didn't array-enter the formulas in that workbook. To do so:

1. Select cell C2

2. Click in the formula bar

3. Hold the Control and Shift keys down

4. Hit Enter, then release all three keys. Excel should respond by adding curly braces { } surrounding your formula. It will also return the desired answer. If not, repeat.

Once the formula in cell C2 works correctly, doubleclick the little square at bottom right corner of the selection marquee. This will copy that formula down until the end of data. For your sample workbook, it will take about 30 seconds for it to finish recalculating and update the display.

Brad