Multiple criteria greater than

[Ruca13]

Hello experts,

I have different license plates belonging to various companies, but that can be "drafted" to other companies. It is easier to explain it sample data:

[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD="align: center"]License Plate[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Start Date[/TD]
[/TR]
[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]01-01-2017[/TD]
[/TR]
[TR]
[TD="align: center"]A2[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]01-01-2017[/TD]
[/TR]
[TR]
[TD="align: center"]A2[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]01-12-2017[/TD]
[/TR]
</tbody>[/TABLE]

On another sheet I have a database with logs for trips, and based on the license and on the trip date, I want to know the company. So on 1-11-2017, A2 would show the company AAA, but the trips starting in December should show the company BBB.

Does anyone know how to do this without arrays?

Thank you for your time,
Rui

 

[JLGWhiz]

How to do what without arrays? I don't understand the objective.

 

[Ruca13]

How to get the company name, based on license plate and on trip date (needs to be greater than the start date I show on the table).

 

[JLGWhiz]

How to get the company name, based on license plate and on trip date (needs to be greater than the start date I show on the table).

I believe you will need either an array formula or VBA to do what you want.

 

[Ruca13]

I believe you will need either an array formula or VBA to do what you want.

I appreciate your advice, I will then solve it with VBA.

Thanks.

 

[JLGWhiz]

This should be copied to your code module1. Sheet1 will be the two column (A:B) sheet and Sheet 2 will be the three column (A:C) sheet. Column A contains tag number in both sheets. When matches are found, the company will be returned to column C of sheet 1.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, fAdr As String
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("C:C").Find(c.Offset(, 1).Value, , xlValues)
            If Not fn Is Nothing Then
                fAdr = fn.Address
                Do
                    If c.Value = fn.Offset(, -2).Value Then
                        c.Offset(, 2) = fn.Offset(, -1).Value
                    End If
                    fn = sh2.Range("C:C").FindNext(fn)
                Loop While fn.Address <> fAdr
            End If
    Next
End Sub

 

[Ruca13]

Thanks I will try it later.

I know basic VBA so my thoughts were more directed to using filters, but your solutions seems neat.

I will postpone that code for a little bit as I already had the need to change the macro through which I add the trips, I will then integrate it inside.

Thank you for your help.

 

[JLGWhiz]

Thanks I will try it later.

I know basic VBA so my thoughts were more directed to using filters, but your solutions seems neat.

I will postpone that code for a little bit as I already had the need to change the macro through which I add the trips, I will then integrate it inside.

Thank you for your help.

You're welcome. It worked OK in the test set up described.
Regards, JLG