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!

 

[Veritan]

Index and Match can be tricky when working with multiple criteria, especially if you're not matching exact dates and times. See if this VBA solution works for you. I'm assuming that Row 1 has headers and that Data Set 1 has a column for Order Number. This puts Data 1 Set in Columns A:C and Data Set 2 in Columns E:H, using column headers in the order you gave (with the exception of Order Number for Data Set 1, which I put in Column C).

Sub OrderNumber()
    Dim r As Range, c As Range
    
    Range("C2", Cells(Rows.Count, 1).End(xlUp).Offset(, 2)).ClearContents
    For Each r In Range("A2", Cells(Rows.Count, 1).End(xlUp))
        For Each c In Range("E2", Cells(Rows.Count, 5).End(xlUp))
            If r = c Then
                If r.Offset(, 1) >= c.Offset(, 2) And r.Offset(, 1) <= c.Offset(, 3) And r.Offset(, 2) = "" Then _
                    r.Offset(, 2) = c.Offset(, 1)
            End If
        Next c
        If r.Offset(, 2) = "" Then r.Offset(, 2) = 0
    Next r
End Sub

I personally prefer using VBA for anything which sounds like it will take a lot of Index/Matching since VBA has a lot more options for finding a match based on your specific criteria.

 

[gsd217]

Thank you for the response. In Data set 1 there was not a column for Order Number. However, I created a new workbook to mimic the VBA that you provided. Putting the columns from both data sets in the order that you gave. I assume we skipped column D, in order to separate the two data sets? The order number column (column c), should have no data prior to running the VBA, correct? After running the code, the order number column (column c) populates with 0's.

 

[Veritan]

Yes, I skipped column D to use it as a separator column, though that can be easily adjusted. Can you give a specific example of what your data looks like? Feel free to use substitute data, as long as the format and cell locations are the same. The data location is obviously really important to making the code run effectively. Also, could you elaborate on what you need the Order Number for, or where it's supposed to end up when the code finds a match? That way, you won't have to adjust your whole worksheet.

 

[gsd217]

Thank you for the continued support with this issue. Below is an example of the data (although not very organized). There are a total of 8 columns below. Column A (EquipID or Truck from Dataset 1), Column B (Date/Time or Toll Transaction Date and Time from Dataset 1), Column C (Order Number, this is blank and should be where the original order number shows up when matched) Column D is blank, Column E (Tractor or Truck from dataset 2), Column F (Original Order Number from dataset 2) Column G (Dispatch Time from dataset 2), Column H (Empty Time from dataset 2). I am attempting to match the order number in column F with the transactions in Columns A:B. For the below example 3502 (row 1) the transaction occurred between the dispatch and empty times and thus the order number needs to be matched in Column C.

I am not too concerned with giving you the exact layout because we can tweak this after the fact. It is a large dataset 50,000+ rows across two worksheets.

Thank you again for all the help!

EquipID	Date/Time	OrderNumber		Tractor	OrderNo	DispatchTime	EmptyTime
3502	12/28/16 4:27 PM	0		3481	0937634	12/28/16 5:09 PM	1/3/17 12:28 PM
3606	12/30/16 7:30 PM	0		3502	0938210	12/27/16 7:22 AM	1/4/17 8:27 AM
3606	12/30/16 6:23 PM	0		3780D	0938350	12/29/16 9:57 AM	1/5/17 6:10 AM

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Veritan]

This may be just a typo, but when you refer to row 1, I see that the Date/Time in column B occurs prior to the DispatchTime. Are you looking to match order numbers when the Date/Time > DispatchTime and Date/Time < EmptyTime?

 

[gsd217]

Yes, precisely. This is what we are trying to achieve. For example 3502 in dataset 1, should be between the dispatch and empty time of the 3502 transaction in dataset 2.

 

[Veritan]

Okay, thank you. You mention that the data is across 2 worksheets. Using multiple worksheets has a pretty big impact on code. Not hard to program, just it needs to be taken into account. Where exactly is your data on each sheet? Specifically, which sheet has which set of data? Are the sheets named, and if so, what is the name for each sheet?

 

[FDibbins]

This might be simpler to work though if you use a helper column to ID the rows you need to consider?

 

[gsd217]

What is meant by a helper column?