VLOOKUP and MIN functions

[JimRoy]

Example worksheets:

I'm trying to resolve for Worksheet1 B2 - B4

I want to find the earliest transaction date in Worksheet2 for each UniqueID in Worksheet1 Column A and place the date in Worksheet1 Column B.

There are only unique ID numbers in A Worksheet 1

Worksheet2 is a transaction table so there are multiple records with the same ID.

I have tried using the VLOOKUP(MIN but am missing something because it just does not work.

I would prefer a function solution vs VBA, if possible.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UniqueID[/TD]
[TD]ActualStartDate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A125[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








Worksheet1


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UniqueID[/TD]
[TD]TransDate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A123[/TD]
[TD]1/13/16[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A123[/TD]
[TD]2/1/16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A124[/TD]
[TD]12/13/16[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A123[/TD]
[TD]9/22/16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A124[/TD]
[TD]10/13/16[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A123[/TD]
[TD]7/25/16[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A125[/TD]
[TD]8/1/16[/TD]
[/TR]
</tbody>[/TABLE]















Worksheet2

Thanks,

 

[JimRoy]

Re: Help with VLOOKUP and MIN functions

The worksheet names are confusing when submitted.

Worksheet1 is the upper table.

 

[Peter_SSs]

Re: Help with VLOOKUP and MIN functions

If you sorted Worksheet2 by TransDate (olsest to newest) you could use VLOOKUP.

However, with your current layout, try this, copied down.

Book1
	A	B
1	UniqueID	ActualStartDate
2	A123	13/01/2016
3	A124	13/10/2016
4	A125	1/08/2016
Worksheet1

Cell Formulas
Range	Formula
B2	=AGGREGATE(15,6,Worksheet2!B$2:B$8/(Worksheet2!A$2:A$8=A2),1)

 

[JimRoy]

Re: Help with VLOOKUP and MIN functions

This is working but with some issues. The actual worksheets I'm using are quite a bit larger that my examples. Worksheet1 has approx 13,000 records and Worksheet2 40,000.

Issues:
1. Not all UniqueIDs in Worksheet1 column A have a match in Worksheet2 column A. It appears these missing matches are causing a #Num ! error in Workseett1 Column A. Any ideas how avoid this? I tried the IFERROR function in front of the AGGREGATE formula but it didn't work.
2. When the AGGREGATE function calculates it takes a long time to complete. > 5 min. Is there anyway to speed this up.

Other info: Both of these Worksheets are pulling data from an ODBC connection via SQL statements. The Worksheet2 TransDate field also uses an IF statement to pull dates from another field located in the same table. Is there a way to control when each refresh and calculations run?

Sorry to be pilling on but, devil is always in the details. Any help would be appreciated.

 

[Peter_SSs]

Re: Help with VLOOKUP and MIN functions

Both of these Worksheets are pulling data from an ODBC connection via SQL statements.

I'm not too familiar with that so can't really offer advice about it.

I tried the IFERROR function in front of the AGGREGATE formula but it didn't work.

Maybe it isn't relevant given the size of your data and the speed issue, but IFERROR works fine for me. For the little sample that I used before, this is what I used:
=IFERROR(AGGREGATE(15,6,Worksheet2!B$2:B$8/(Worksheet2!A$2:A$8=A2),1),"Not found")

Worksheet1 has approx 13,000 records and Worksheet2 40,000.
When the AGGREGATE function calculates it takes a long time to complete. > 5 min. Is there anyway to speed this up.

I don't know how dynamic the results need to be, but you could try this macro in a copy of your workbook.

Sub ActualStartDate()
  Dim d As Object
  Dim a As Variant
  Dim i As Long

  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Worksheet2")
    a = .Range("A2", .Range("B" & .Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    If d.exists(a(i, 1)) Then
      If a(i, 2) < d(a(i, 1)) Then d(a(i, 1)) = a(i, 2)
    Else
      d(a(i, 1)) = a(i, 2)
    End If
  Next i
  With Sheets("Worksheet1")
    a = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value
    ReDim Preserve a(1 To UBound(a), 1 To 2)
    For i = 1 To UBound(a)
      If d.exists(a(i, 1)) Then
        a(i, 2) = d(a(i, 1))
      Else
        a(i, 2) = "Not found"
      End If
    Next i
    .Range("A2").Resize(UBound(a), 2).Value = a
  End With
End Sub

 

[JimRoy]

Re: Help with VLOOKUP and MIN functions

I apologize for not being timely but, this worked great. Thank you.

 

[Peter_SSs]

Re: Help with VLOOKUP and MIN functions

I apologize for not being timely but, this worked great. Thank you.

No problem. Glad it helped.