Solved

Find a date

Posted on 2013-05-24
11
189 Views
Last Modified: 2013-05-27
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
0
Comment
Question by:Jagwarman
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 39193609
@max(xx:xx) will do it but replicating for each group is out of my scope
q-28138022.xlsx
0
 
LVL 7

Expert Comment

by:MSmax
ID: 39193646
Hey,
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:$O$5,0))}
Change it to C1 if you don't use headers.
(to make it array formula press CTRL+SHIFT+ENTER)
Max-Array.xls
0
 
LVL 9

Expert Comment

by:jsdray
ID: 39194058
and then to sort it after the max is completed...
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

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 39194209
The formula "=MAX" returns every cell with the same date 23/5/2013

The Macro 'Macro1' removes the header in row 1

is this because I am using 2010?
0
 

Author Comment

by:Jagwarman
ID: 39194447
On further investigation the result in the formula box shows the correct answer of 17/5/2013 but the result shown in the spreadsheet is showing as 24/5/2013.

bizzare
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Jagwarman
ID: 39194533
Have attached spreadsheet to assist and show what I mean. Hope someone can resolve this for me.
Book3.xlsx
0
 
LVL 7

Expert Comment

by:MSmax
ID: 39194741
Hey,
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
0
 
LVL 80

Expert Comment

by:byundt
ID: 39195175
Here are some user-defined functions that will return the maximum value in a range subject to a criteria. Install them in a regular module sheet, then use them with a worksheet formula like:
=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

Open in new window

Brad
MaxIfQ28138002.xlsm
0
 

Author Comment

by:Jagwarman
ID: 39198822
Hi msmax

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?
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39198951
jagwarman,
In your earlier Comment http://www.experts-exchange.com/Q_28138002.html#a39194533 you complained:
Have attached spreadsheet to assist and show what I mean. Hope someone can resolve this for me.
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
0
 

Author Closing Comment

by:Jagwarman
ID: 39198995
Thanks for that, sorry I was such a dumb ass.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now