Index, Match and IF Statement Help-

[gsd217]

Hello I am having issues with the following:

I have two data sets. Both contain transactions and neither have unique values. Data set 1 contains Truck, and Toll Transaction Date/Time. Data set 2 contains Truck, Order Number, Dispatch Date/Time, and Empty Date/Time.

I am attempting to index the Order Number from Data set 2 if the criteria is met in Data set 1. For example:

=INDEX(Order Number, Match(IF(AND(Truck(Data Set 1)=Truck(Data Set 2), Toll Transaction Date/Time>Dispatch Date/Time, Toll Transaction Date/Time< Empty Date/Time),0)


I am not sure if this is close, but logically in order to index the order number, I would have to ensure that the "IF" conditions are met?

Thank you in advance for the help!

 

[gsd217]

The two sheets are "Data" and "PrePass Data" Dataset 1 is in the "PrePass Data" sheet and Dataset 2 is in the "Data" sheet. For Datasheet 1, Truck is in column H, Transaction date/time is in column S and order is now in column U. For the "Data" worksheet, Truck is in column AA, Order Number is in column A, Dispatch Date/Time is in column D and Empty date/time is in column F.

Thank you!

 

[FDibbins]

I helper column is a column you use to put calcs in that will help simplify doing what you want. So in this case, maybe do a test to pull out only those rows that have a matching Order number, then use that column for the rest of the calc?

I think it might help if you show a sample of all your data, as well as what you expect

 

[Veritan]

Okay, I believe this will work for you. I've set it up based on the column values you gave me, so I don't think it will need any editing. Try this out and see if it works for you. Let me know if it needs any more work. Good luck!

Sub OrderNumber()
    Dim r As Range, Truck, FirstAddress$, wsPP As Worksheet, wsData As Worksheet
    
    Set wsPP = Sheets("PrePass Data")
    Set wsData = Sheets("Data")
    
    Range(wsPP.Cells(2, 21), wsPP.Cells(Rows.Count, 21).End(xlUp).Offset(1)).ClearContents
    For Each r In Range(wsPP.Cells(2, 8), wsPP.Cells(Rows.Count, 8).End(xlUp))
        With Range(wsData.Cells(2, 27), wsData.Cells(Rows.Count, 27).End(xlUp))
            Set Truck = .Find(What:=r, LookAt:=xlWhole)
            If Not Truck Is Nothing Then
                FirstAddress = Truck.Address
                Do
                    If r.Offset(, 11) >= Truck.Offset(, -23) And r.Offset(, 11) <= Truck.Offset(, -21) Then
                        r.Offset(, 13) = Truck.Offset(, -26)
                    Else: Set Truck = .FindNext(Truck)
                    End If
                Loop While Not Truck Is Nothing And r.Offset(, 13) = "" And Truck.Address <> FirstAddress
            End If
            If r.Offset(, 13) = "" Then r.Offset(, 13) = 0
        End With
    Next r
End Sub

 

[ChandraAmgain]

If i understand you.........
Do you want to match and return value from other sheet? (Data set 2) if so index match is fine to you. i have little bit problem with language but i hope, i am able to solve your question with Formula only. Please attached your both sheet Sample file. so i can create a sample file.

 

[Veritan]

Edit: I thought the loop structure looked odd, so I took another look at it. In certain scenarios, it could get stuck in an infinite loop. Use this code instead of the original.

Sub OrderNumber()
    Dim r As Range, Truck, FirstAddress$, wsPP As Worksheet, wsData As Worksheet
    
    Application.ScreenUpdating = False
    Set wsPP = Sheets("PrePass Data")
    Set wsData = Sheets("Data")
    
    Range(wsPP.Cells(2, 21), wsPP.Cells(Rows.Count, 21).End(xlUp).Offset(1)).ClearContents
    For Each r In Range(wsPP.Cells(2, 8), wsPP.Cells(Rows.Count, 8).End(xlUp))
        With Range(wsData.Cells(1, 27), wsData.Cells(Rows.Count, 27).End(xlUp))
            Set Truck = .Find(What:=r, LookAt:=xlWhole)
            If Not Truck Is Nothing Then
                FirstAddress = Truck.Address
                Do
                    If r.Offset(, 11) >= Truck.Offset(, -23) And r.Offset(, 11) <= Truck.Offset(, -21) Then _
                        r.Offset(, 13) = Truck.Offset(, -26)
                    Set Truck = .FindNext(Truck)
                Loop While Not Truck Is Nothing And Truck.Address <> FirstAddress
            End If
            If r.Offset(, 13) = "" Then r.Offset(, 13) = 0
        End With
    Next r
    Application.ScreenUpdating = True
End Sub

Sorry about the mix-up, this should work better.