• Status: Solved
• Priority: Medium
• Security: Public
• Views: 421

# Index/Match formula

I'm trying to look up values that are between two dates. In columns B-D I have my first list of data and then in columns F-H I have my second list. In column M is my dates for every Friday through the next year and a half. The results I am looking for are in row 6 in columns N-P.

Example: The Friday day in cell M6 is 9/14/12 so I need to find the order in either the first list (Columns B-D) or the second list (Columns F-H) that either equals 9/14/12 or is less than 9/14/12 but greater than 9/7/12 (Cell M5). When it finds that order I need the TOTAL LBS of that order put into column N, the DUE DATE put into column O, and the ORDER # put into column P. I need the two different lists for other reasons so I cannot make them into 1 list.

There is a catch to this. There could be more than 1 order due within that same week so I would need to combine them into 1 row in columns N-P but use the later of the 2 due dates and total the LBS and use a "/" to split the order numbers.
Sample.xlsx
0
Lawrence Salvucci
• 6
• 5
1 Solution

Commented:
try this code...

I have hardcoded the Orders ranges for now during testing. These can be changed to methods that find the extend of data.

``````Sub Output1()

Dim shipdt As Range, prevweek As Date, duedate As Date
Dim fnd1 As Variant
Dim Orders1 As Range, Orders2 As Range

Set Orders1 = ActiveSheet.Range("A2:A9")
Set Orders2 = ActiveSheet.Range("E2:E2")

Set shipdt = ActiveSheet.Range("L4")
Dim dt1 As Date
Do While Not IsEmpty(shipdt)
If shipdt.Offset(-1, 0).Value = "Balance" Then
'is the first cell, so dont do anything
Else
prevweek = shipdt.Offset(-1, 0).Value
totallbs = 0
duedate = 0: orderno = ""

For dt1 = prevweek + 1 To shipdt.Value
With Orders1
strdate = Format(dt1, .Cells(1, 1).NumberFormat)
Set fnd1 = .Find(What:=strdate, SearchOrder:=xlByRows, LookIn:=xlValues)
If Not fnd1 Is Nothing Then
Do
totallbs = totallbs + fnd1.Offset(0, 2).Value
duedate = fnd1.Offset(0, 0).Value
orderno = fnd1.Offset(0, 1).Value
Set fnd1 = .FindNext

End If
End With
With Orders2
strdate = Format(dt1, .Cells(1, 1).NumberFormat)
Set fnd1 = .Find(What:=strdate, SearchOrder:=xlByRows, LookIn:=xlValues)
If Not fnd1 Is Nothing Then
Do
totallbs = totallbs + fnd1.Offset(0, 2).Value
duedate = fnd1.Offset(0, 0).Value
orderno = fnd1.Offset(0, 1).Value
Set fnd1 = .FindNext

End If
End With

Next dt1
If totallbs > 0 Then
shipdt.Offset(0, 1) = totallbs
shipdt.Offset(0, 2) = duedate
shipdt.Offset(0, 3) = orderno
End If
End If
Set shipdt = shipdt.Offset(1, 0)

Loop
End Sub
``````
0

Information Technology ManagerAuthor Commented:
That works perfect but what about when there are 2 on the same date? How can I get it to combine the total and split the Order#'s for that date using a "/"?
0

Commented:
forgot the bit about combining order #,  total works fine (tested for 12/31/13 but need to extend your ShipDate range)

change lines 29 & 42 to be
``````   orderno = orderno & fnd1.Offset(0, 1).Value
``````
0

Commented:
and change 51 - 55 to
``````            If totallbs > 0 Then
shipdt.Offset(0, 1) = totallbs
shipdt.Offset(0, 2) = duedate
shipdt.Offset(0, 3) = Mid(orderno, 2)
Else
shipdt.Offset(0, 1).ClearContents
shipdt.Offset(0, 2).ClearContents
shipdt.Offset(0, 3).ClearContents
End If
``````
0

Information Technology ManagerAuthor Commented:
That didn't work. I tested it by changing the date2 on 2 of the orders to see if the total combines and the order #'s are combined using a "/" to separate them but it didn't work with your new code modifications.
0

Information Technology ManagerAuthor Commented:
Actually it's working but when it loads the first order # it's dropping the "L" from the order # when it's loading it into column O. Also, there could be the same date in column A so can you modify your code to have it combine them if they are in column A and/if in column E? Here's the sample again with the "L" missing.
Sample.xlsm
0

Commented:
you must not have had the changes correct as it works for me.

here is the updated full code, including getting the size of Duedate ranges.
``````Sub Output2()
'version 2  ... 3Sep2012
Dim shipdt As Range, prevweek As Date, duedate As Date
Dim fnd1 As Variant
Dim Orders1 As Range, Orders2 As Range

'Set Orders1 = ActiveSheet.Range("A2:A9")
'Set Orders2 = ActiveSheet.Range("E2:E2")

Set Orders1 = GetUsedCells(ActiveSheet.Range("A2"))
Set Orders2 = GetUsedCells(ActiveSheet.Range("E2"))

Set shipdt = ActiveSheet.Range("L4")
Dim dt1 As Date
Do While Not IsEmpty(shipdt)
If shipdt.Offset(-1, 0).Value = "Balance" Then
'is the first cell, so dont do anything
Else
prevweek = shipdt.Offset(-1, 0).Value
totallbs = 0
duedate = 0: orderno = ""

For dt1 = prevweek + 1 To shipdt.Value
With Orders1
strdate = Format(dt1, .Cells(2, 1).NumberFormat)
Set fnd1 = .Find(What:=strdate, SearchOrder:=xlByRows, LookIn:=xlValues)
If Not fnd1 Is Nothing Then
Do
totallbs = totallbs + fnd1.Offset(0, 2).Value
duedate = fnd1.Offset(0, 0).Value
orderno = orderno & "/" & fnd1.Offset(0, 1).Value
Set fnd1 = .FindNext

End If
End With
With Orders2
strdate = Format(dt1, .Cells(2, 1).NumberFormat)
Set fnd1 = .Find(What:=strdate, SearchOrder:=xlByRows, LookIn:=xlValues)
If Not fnd1 Is Nothing Then
Do
totallbs = totallbs + fnd1.Offset(0, 2).Value
duedate = fnd1.Offset(0, 0).Value
orderno = orderno & "/" & fnd1.Offset(0, 1).Value
Set fnd1 = .FindNext

End If
End With

Next dt1
If totallbs > 0 Then
shipdt.Offset(0, 1) = totallbs
shipdt.Offset(0, 2) = duedate
shipdt.Offset(0, 3) = Mid(orderno, 2)
Else
shipdt.Offset(0, 1).ClearContents
shipdt.Offset(0, 2).ClearContents
shipdt.Offset(0, 3).ClearContents
End If
End If
Set shipdt = shipdt.Offset(1, 0)

Loop
End Sub

Function GetUsedCells(myCell As Range) As Range

Dim myTop As Range, myBottom As Range, myResult As Range

If myCell.Row = myCell.Worksheet.Rows.Count Then
If IsEmpty(myCell.Offset(-1, 0)) Then
Set myTop = myCell
Else
Set myTop = myCell.End(xlUp)
End If
Else
Set myTop = myCell.Offset(1, 0).End(xlUp)
End If

If myCell.Row = 1 Then
If IsEmpty(myCell.Offset(1, 0)) Then
Set myBottom = myCell
Else
Set myBottom = myCell.End(xlDown)
End If
Else
Set myBottom = myCell.Offset(-1, 0).End(xlDown)
End If

Set myResult = Range(myTop, myBottom)
Set GetUsedCells = myResult

End Function
``````
0

Information Technology ManagerAuthor Commented:
That works great! One last question...say the orders were in another file, how would I change this code to point to it?

Set Orders1 = GetUsedCells(ActiveSheet.Range("A2"))
Set Orders2 = GetUsedCells(ActiveSheet.Range("E2"))
0

Commented:
Just run the macro with the desired file active. That's what activesheet refers to.
0

Commented:
This assumes that the orders start at a2 and E5 in s all sheets .
0

Information Technology ManagerAuthor Commented:
Thank you for all your help! Much appreciated!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.