[VBA] Find Values (Date) in Dynamic Range

[Joeseph]

Hello Excel-experts,

i need some help because this problem is too tricky for me. First my Data. In Sheet("table1") I have the following data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number
[/TD]
[TD]Position
[/TD]
[TD]Date-WE
[/TD]
[TD]Date-XY
[/TD]
[/TR]
[TR]
[TD]10010
[/TD]
[TD]10
[/TD]
[TD]10.10.2014
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10010
[/TD]
[TD]10
[/TD]
[TD]12.10.2014
[/TD]
[TD]24.11.2014
[/TD]
[/TR]
[TR]
[TD]10010
[/TD]
[TD]20
[/TD]
[TD]09.10.2014
[/TD]
[TD]25.12.2014
[/TD]
[/TR]
[TR]
[TD]10011
[/TD]
[TD]10
[/TD]
[TD]08.10.2014
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10011
[/TD]
[TD]10
[/TD]
[TD]04.09.2014
[/TD]
[TD]26.11.2014
[/TD]
[/TR]
[TR]
[TD]10011
[/TD]
[TD]10
[/TD]
[TD]08.10.2014
[/TD]
[TD]28.12.2014
[/TD]
[/TR]
[TR]
[TD]10012
[/TD]
[TD]20
[/TD]
[TD]10.10.2014
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the Sheets("table2") I already evaluated how often the number (column A) with it's (Position) is written in Sheets("table1").
It's looking like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Number
[/TD]
[TD]Position
[/TD]
[TD]#Quantity
[/TD]
[TD]Earliest WE-Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10010
[/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10010
[/TD]
[TD]20
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10011
[/TD]
[TD]10
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10012
[/TD]
[TD]20
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now my problem is to evaluate the earliest WE-Date. I Need to check Sheets("table2).(A2:B2) (Number 10010 and Position 10) in Sheets("table1") and write the earliest date into Sheets("table2").C2. In this case the earliest date is 10.10.2014. Then jump to the next one (A3:B3) and so on until the last one!

I appreciate every help I can get, as this is quite the task for me.

Thank you guys!

 

[Rent23]

Try this:

Sub earliest_date()

For xx = 2 To Sheets("table2").Range("A" & Rows.Count).End(xlUp).Row
Sheets("table1").Select
For zz = 2 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & zz) = Sheets("table2").Range("A" & xx) And Range("B" & zz) = Sheets("table2").Range("B" & xx) Then
EaDateWE = DateSerial(Right(Range("C" & zz), 4), Mid(Range("C" & zz), 4, 2), Left(Range("C" & zz), 2))
    For yy = zz + 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & yy) = Sheets("table2").Range("A" & xx) And Range("B" & yy) = Sheets("table2").Range("B" & xx) Then
            DateWE = DateSerial(Right(Range("C" & yy), 4), Mid(Range("C" & yy), 4, 2), Left(Range("C" & yy), 2))
            If DateWE < EaDateWE Then EaDateWE = DateWE
        End If
    Next yy
    GoTo Nextv
End If
Next zz
Nextv:
Sheets("table2").Range("D" & xx) = Day(EaDateWE) & "." & Month(EaDateWE) & "." & Year(EaDateWE)
    
Next xx

End Sub

 

[Joeseph]

Hey Rent23,

you're amazing! It works perfectly. But there is one little problem. I have about 4500 cells in column A to check. in Table1 are about 60000 values to search.. It takes a whole lot of time to get the Dates. Is there any way to calculate the earliest day faster? And what do i have to change in the code to get the latest date?

 

[Rent23]

Joeseph, if you have 60k values to search, it is pretty difficult to find a faster code.

Maybe there is a way but the main issue here is your huge database not the slowness of the code.

For get the latest date change:

If DateWE < EaDateWE Then EaDateWE = DateWE

With

If DateWE > EaDateWE Then EaDateWE = DateWE

 

[Joeseph]

Yeah it's a huge mass to search. I found an alternative, just for the guys interested in the solution: {=MIN(WENN('1Q2014'!$A$2:$A$70000=$A2;WENN('1Q2014'!$B$2:$B$70000=$B2;'1Q2014'!$F$2:$F$70000;"")))} for each cell. It's way faster and the results are the same. I think Wenn = if, as my Excel is in german. But i'll Keep your code for a later time as I think i will need it, not just for this calculation!

I really appreciate your effort :D THANKS!