Coding: Excel VBA

Clauric

Not sure if this is the appropriate forum (if it is not, please move).

I have a file with 3 columns. Column A contains 300,000 rows, with about 200 separate IDs, all duplicated at least 1,000 times. Column B contains the date for each of the rows. Column C contains the values that I need to extract.



Each of the 200 IDs in Col A can have multiple values (e.g. ID 1234 might have dates 1/1/2001, 1/3/2001, 1/2/2015, etc). Similarly, each date on Col B will have multiple IDs (e.g. 1/2/15 might have IDs of 1234, 1874, 1930, 6043, etc).


In a nutshell, I need to check the values in Col A and Col B to find the relevant ID in Col A and the maximum value in Col B, and return the value in the relevant cell in Col C.


I've looked at Index/Match examples, but they don't seem to be suitable. Is there any suggestions on a macro I could run, that would accomplish what is needed.

stimpson

Is this something you need to do on a regular basis, or just once?

I'm not entirely clear on your ask - do you just want the value for each ID with the latest date? If so you could sort by Col A then Col B and read off the values like that.

If you need a macro then post some sample data and I'll take a gander.

Clauric

stimpson wrote: »

Is this something you need to do on a regular basis, or just once?

I'm not entirely clear on your ask - do you just want the value for each ID with the latest date? If so you could sort by Col A then Col B and read off the values like that.

If you need a macro then post some sample data and I'll take a gander.

Yes, it is something that is needed on a regular basis. The sort function would not work, as the specific order is needed for other work being done as part of the same macro (often need to find the next entry in the list, etc).

I've attached a sample data for your perusal.

stimpson

Clauric wrote: »

Yes, it is something that is needed on a regular basis. The sort function would not work, as the specific order is needed for other work being done as part of the same macro (often need to find the next entry in the list, etc).

I've attached a sample data for your perusal.

There are 2 ways around that - add a 4th column with the row number - 1,2,3,4 etc. Then you can sort against that when you're done.

Otherwise you just don't save the spreadsheet once you're done.

stimpson

This seems to work, but please do some testing as I'm a useless tester.

Use at your own risk - don;t blame me if it all goes tits up!

Option Explicit

Sub GetVal()
    Dim c As Range
    Dim dict As Object
    Dim a As Variant
    Dim Item() As Variant
    Dim DictItem() As Variant
    Dim key As Variant
    Dim varJoin As String
    Dim i As Long
    
    Set dict = New Scripting.Dictionary
       
    For Each c In ActiveSheet.UsedRange.Columns("A").Cells
        If c <> "ID" Then
            Item = Range(c, c.Offset(0, 2))
            If (dict.Count = 0) Then
                dict.Add key:=c.Value, Item:=Item
            Else
                If dict.Exists(c.Value) Then
                    DictItem = dict(c.Value)
                    If DictItem(1, 2) < Item(1, 2) Then
                        dict(c.Value) = Item
                    End If
                Else
                    dict.Add key:=c.Value, Item:=Item
                End If
            End If
        End If
            
    Next c
    
    
    a = dict.Items
    For i = 0 To dict.Count - 1
        ActiveSheet.Range("E2").Offset(i, 0) = a(i)(1, 1)
        ActiveSheet.Range("E2").Offset(i, 1) = a(i)(1, 2)
        ActiveSheet.Range("E2").Offset(i, 2) = a(i)(1, 3)
    Next i
End Sub

stimpson

Oops. Should have mentioned it uses Microsoft Scripting Runtime. To enable it go this from the VBA window:

Select Tools > References from the drop-down menu
A listbox of available references will be displayed
Tick the check-box next to 'Microsoft Scripting Runtime'