VLOOKUP and MIN functions

JimRoy

New Member
Joined
Jul 17, 2015
Messages
11
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.

AB
1UniqueIDActualStartDate
2A123
3A124
4A125

<tbody>
</tbody>








Worksheet1


AB
1UniqueIDTransDate
2A1231/13/16
3A1232/1/16
4A12412/13/16
5A1239/22/16
6A12410/13/16
7A1237/25/16
8A1258/1/16

<tbody>
</tbody>















Worksheet2

Thanks,
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JimRoy

New Member
Joined
Jul 17, 2015
Messages
11
Re: Help with VLOOKUP and MIN functions

The worksheet names are confusing when submitted.

Worksheet1 is the upper table.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,416
Office Version
365
Platform
Windows
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">UniqueID</td><td style=";">ActualStartDate</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A123</td><td style="text-align: right;;">13/01/2016</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A124</td><td style="text-align: right;;">13/10/2016</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A125</td><td style="text-align: right;;">1/08/2016</td></tr></tbody></table><p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Worksheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">15,6,Worksheet2!B$2:B$8/(<font color="Red">Worksheet2!A$2:A$8=A2</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
 

JimRoy

New Member
Joined
Jul 17, 2015
Messages
11
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,416
Office Version
365
Platform
Windows
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.
Code:
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

New Member
Joined
Jul 17, 2015
Messages
11
Re: Help with VLOOKUP and MIN functions

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

Watch MrExcel Video

Forum statistics

Threads
1,095,360
Messages
5,444,012
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top