I need to do an approximate lookup function that will return only unique values.

[nikkigotro]

I am working on a project in Excel 2013 in which I have data on two separate tabs. I am looking at the time stamp that a call was placed in one of our systems and am trying to use a lookup to find the closest corresponding time stamp on the orders data (on the other sheet) and return a unique order number for each call. I can get order numbers to pull over using both an INDEX MATCH and a VLOOKUP, but about 1/3 of my results are duplicates. Any help that I could get would be greatly appreciated!

Sheet 1

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Timestamp[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD]41556.003125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="align: right"]2219680408[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD]41556.0065740741[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="align: right"]2218774489[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD]41556.1103009259[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="align: right"]2219672856[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD]41556.5734837963[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="align: right"]2219670371[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Timestamp[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 136"]
<tbody>[TR]
[TD="class: xl65, width: 136"]41556.2737037037[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 136"]
<tbody>[TR]
[TD="class: xl65, width: 136"]41556.321087963[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 136"]
<tbody>[TR]
[TD="class: xl65, width: 136"]41556.3980439815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 136"]
<tbody>[TR]
[TD="class: xl65, width: 136"]41556.425462963[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The "Order" Column on the second sheet is the one I am trying to fill in based on approximate data in the first sheet (there should not be any cases where these match exactly). I just need unique order numbers in my second sheet. Also, the very first timestamp produces N/A error because there is not an order with a timestamp that comes in previous to that call. Is there a fix for this as well?

Thank you in advance!!!

 

[bruderbell]

So the earliest timestamp on sheet 2 should get the order with the earliest timestamp from sheet 1, and the second earliest getst he second earliest, and so forth?

 

[nikkigotro]

So the earliest timestamp on sheet 2 should get the order with the earliest timestamp from sheet 1, and the second earliest getst he second earliest, and so forth?

Yes, that is correct. I just need the order number with the closest matching time stamp to be returned on sheet 2, but I need them to all be unique.

 

[bruderbell]

I've had fun figuring out this formula. I hope it is helpful:

=INDEX(Sheet1!B2:B5,MATCH(COUNTIF(B2:B5,"<"&B2:B5)+1,COUNTIF(Sheet1!A2:A5,"<"&Sheet1!A2:A5)+1,0))

Paste it into Sheet2!A1, then highlight A1:A4, then click into formula bar and ctrl shift enter.

I think it's returning the orders as requested. Please confirm.

For learning's sake, I used this page as a reference: http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/

 

[nikkigotro]

I've had fun figuring out this formula. I hope it is helpful:

=INDEX(Sheet1!B2:B5,MATCH(COUNTIF(B2:B5,"<"&B2:B5)+1,COUNTIF(Sheet1!A2:A5,"<"&Sheet1!A2:A5)+1,0))

Paste it into Sheet2!A1, then highlight A1:A4, then click into formula bar and ctrl shift enter.

I think it's returning the orders as requested. Please confirm.

For learning's sake, I used this page as a reference: Sorting text cells using array formula in excel | Get Digital Help - Microsoft Excel resource

First of all, thank you so much for replying! I have been working on this non-stop for days. I put your formula in and it did return order numbers, but it gave me over 80 duplicate order numbers still. I am going to attach a link to my file with data included so that you can see exactly what I am working with. If you don't mind to take a look at it, I would greatly appreciate it. I put in conditional formatting to show duplicate order numbers in red. https://skydrive.live.com/redir?resid=AFDB5CF6630F32EE!281&authkey=!ADKSVSUcyp1wGXE

 

[nikkigotro]

Can anyone help? I am still not able to figure this out, unfortunately.

 

[bruderbell]

So I see that your dataset has 5000+ orders and only 500+ calls (vs a matching quantity as shown in teh subset). The formula I put together returns order 1 (earliest) to the earlist call, 2nd to 2nd, and so forth. It does not actually compare the date of the order to the date of the call.

Let me try to rephrase your goal to make sure I understand it. You want to return the nearest order number that comes immediately [before/after] the timestamp, with no duplicates being returned.

I think a user-defined function will be able to do that. In order to not return duplicates, would you want it to just look at the values above the current value on the list? Or the values below?

 

[nikkigotro]

So I see that your dataset has 5000+ orders and only 500+ calls (vs a matching quantity as shown in teh subset). The formula I put together returns order 1 (earliest) to the earlist call, 2nd to 2nd, and so forth. It does not actually compare the date of the order to the date of the call.

Let me try to rephrase your goal to make sure I understand it. You want to return the nearest order number that comes immediately [before/after] the timestamp, with no duplicates being returned.

I think a user-defined function will be able to do that. In order to not return duplicates, would you want it to just look at the values above the current value on the list? Or the values below?

Yes, you are exactly correct about my goal. There are multiple rows listed for each order because that data set is including any time a person made any changes or even looked at an order. I just need to know a unique order number that has a time stamp close to the time stamp listed for calls, so it doesn't matter to me if it is above or below that time frame, as long as it is close and is not returned more than one time. I really appreciate your help on this!

 

[bruderbell]

I've designed a function that does this, but I need help from the community. Can some smart coder look at this and help make it more efficient?

Brief description of what it does:
Reads in the time values and order numbers from sheet 1, then subtracts the search time from every time. Sorts that list in descending order, then starts at the bottom of the list and checks if that value has already been used. Spits out the first value that hasn't been used.

The sorting of the internal array in the VBA code seems to take a long, long time. As you can see in the commented section of code, I can accomplish the sort using excel's sort function when doing this as a macro. But UDFs won't let me create a new sheet, etc.

Function OrderReturn(LookupTime As Date, TimeRange As Range, OrderRange As Range, Optional CompareRange As Range)
Dim HowClose As Variant
HowClose = TimeRange.Value2
ReturnRange = OrderRange.Value2

'This part gives the difference between the TimeRange and the LookupTime
For i = 1 To UBound(HowClose)
HowClose(i, 1) = Abs(HowClose(i, 1) - LookupTime)
Next i

Dim testarray()
ReDim testarray(1 To UBound(HowClose), 1 To 2)
For h = 1 To UBound(HowClose)
testarray(h, 1) = HowClose(h, 1)
testarray(h, 2) = ReturnRange(h, 1)
Next h


'This section sorts the array so the code knows which choices are closest in time to the target time

    Dim First As Integer, Last As Integer
    Dim FirstCol As Integer, LastCol As Integer
     
    Dim lTemp
     
    First = LBound(testarray, 1)
    Last = UBound(testarray, 1)
    FirstCol = LBound(testarray, 2)
    LastCol = UBound(testarray, 2)
     
    For i = First To Last - 1
        For j = i + 1 To Last
'The 1 defines which columnn is used to sort
'Use > for smallest to largest order; < for largest to smallest
            If testarray(i, 1) < testarray(j, 1) Then
                For k = FirstCol To LastCol
                    lTemp = testarray(j, k)
                    testarray(j, k) = testarray(i, k)
                    testarray(i, k) = lTemp
                Next k
            End If
        Next j
    Next i

Dim ResultsArray()
ReDim ResultsArray(1 To UBound(testarray))
For h = 1 To UBound(testarray)
ResultsArray(h) = testarray(h, 2)
Next h

'ResultsArray = Application.Transpose(ResultsArray1)
'Application.DisplayAlerts = False
'Sheets(Sheets.Count).Delete
'Application.DisplayAlerts = True

'This writes out the array to a new sheet, sorts it, reads back in the array, and deletes the sheet
'Sheets.Add after:=Sheets(Sheets.Count)
'Sheets(Sheets.Count).Range(Cells(2, 1), Cells(UBound(HowClose), 1)) = HowClose
'Sheets(Sheets.Count).Range(Cells(2, 2), Cells(UBound(ReturnRange), 2)) = ReturnRange
'Worksheets(Sheets.Count).Sort.SortFields.Clear
'Worksheets(Sheets.Count).Sort.SortFields.Add Key:=Range("A1"), _
'    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
'    xlSortTextAsNumbers
'With Worksheets(Sheets.Count).Sort
'    .SetRange Range(Cells(1, 1), Cells(UBound(HowClose), 2))
'    .Header = xlNo
'    .MatchCase = False
'    .Orientation = xlTopToBottom
'    .SortMethod = xlPinYin
'    .Apply
'End With

'this section checks if the best answer (closest in time to the lookuptime) has already been used
'disregards it if it has, and writes out the best answer not yet used
On Error Resume Next
1:
Err.Clear
If WorksheetFunction.CountA(CompareRange) = 0 Then
OrderReturn = ResultsArray(UBound(ResultsArray))
Exit Function
End If
x = WorksheetFunction.Match(ResultsArray(UBound(ResultsArray)), CompareRange, 0)
If Err.Number = 0 Then
ReDim Preserve ResultsArray(1 To UBound(ResultsArray) - 1)
GoTo 1:
Else
OrderReturn = ResultsArray(UBound(ResultsArray))
End If
End Function

Who knows how to make this better? The formula will need to be in 500 cells - the long runtime is really going to bog down a computer.

 

[bruderbell]

Try this UDF:

Function UniqueMatch(LookupTime As Range, TimeRange As Range, _
    OrderRange As Range, Optional CompareRange As Range)
a = 0
TimeArray = TimeRange.Value2

Dim TimeDiffArray()
ReDim TimeDiffArray(1 To UBound(TimeArray))
For i = 1 To UBound(TimeArray)
TimeDiffArray(i) = Abs(TimeArray(i, 1) - LookupTime)
Next i
Dim CompareArray As Variant
On Error Resume Next
Err.Clear
comparray = CompareRange.Value2
If UBound(comparray) Then q = 1
If Err.Number <> 0 Then
ReDim CompareArray(1)
CompareArray(1) = comparray(1, 1)
Else
ReDim CompareArray(1 To UBound(comparray))
For i = 1 To UBound(comparray)
CompareArray(i) = comparray(i, 1)
Next i
End If
1:
a = a + 1
UniqueMatch = WorksheetFunction.Index(OrderRange, WorksheetFunction.Match(WorksheetFunction. _
    Small(TimeDiffArray, a), TimeDiffArray, 0))
On Error Resume Next
Err.Clear
If CompareRange Is Nothing Then Exit Function
x = WorksheetFunction.Match(UniqueMatch, CompareArray, 0)
If Not IsNumeric(x) Then Exit Function
If Err.Number <> 0 Then Exit Function
If a = 10 Then Exit Function
GoTo 1:
End Function

That does the exact same thing as my prior post, but runs like a thousand times faster. To use it, enter =uniquematch(LookupTime, TimeRange, OrderRange, CompareRange) where
LookupTime is the single time you are searching for (from your calls sheet, current row)
TimeRange is the range of times to search (from your orders sheet)
OrderRange is the range of Orders (from your orders sheet)
CompareRange is the range of already-used results values (from your calls sheet, current column, above the current result)

I'm clearly a nerd, as I couldn't let this rest without improvement.