vba find the latest date and a corresponding value

[asdasdsad]

Hi all,

I'm in the process of trying to figure out how to find the max date of a value in column A. From there I need to link up the price/volatility/other variables associated with that date.

MaxDate.jpg



I managed to find this code from other forum but it can only do for one column. What if I wish to add more columns after price?

Sub test()
Dim a, i As Long, e, n As Long
a = Sheets("sheet1").Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1: n = 1
For i = 2 To UBound(a, 1)
If a(i, 1) <> "" Then
If Not .exists(a(i, 1)) Then
Set .Item(a(i, 1)) = _
CreateObject("System.Collections.SortedList")
End If
.Item(a(i, 1))(a(i, 2)) = a(i, 3)
End If
Next
For Each e In .keys
n = n + 1: a(n, 1) = e
a(n, 2) = .Item(e).GetKey(.Item(e).Count - 1)
a(n, 3) = .Item(e).GetByIndex(.Item(e).Count - 1)
Next
End With
Sheets("sheet2").Cells(1).Resize(n, 3).Value = a
End Sub

[sassora]

Have you tried this approach and avoiding VBA? https://exceljet.net/formula/max-if-criteria-match

[asdasdsad]

Hey sassora,

Many thanks. The problem is I have more than 1m rows and it crashes everytime:<

[sassora]

The code looks like a hack for the situation you initially gave, not sure you can extend that (?)
If you have a dataset that size then you should consider when Excel is the tool for analysis. R is an option or SQL.

Just to note that this would be simple in R ...

library(dplyr)library(readxl)


df <- read_excel("SKUdata.xlsx")


df %>% 
  group_by(`SKU#`) %>% 
  summarise(maxdate = max(`Effective Date`))

Just to note that this would be simple in R
[CODE]

Over and out!

[p45cal]

Perfect for Power Query; attach a file.

[annonymous33]

Have you tried this approach and avoiding VBA? https://exceljet.net/formula/max-if-criteria-match

There is also a native formula maxif()

I believe its quite new.

[sassora]

yes the link mentions an array formula and also MAXIFS()

[sassora]

Perfect for Power Query; attach a file.

This is very cool:
https://www.youtube.com/watch?v=lwJi...ature=youtu.be