Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Index/Match formula

Posted on 2012-08-30
Medium Priority
416 Views
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
Question by:Lawrence Salvucci
• 6
• 5

LVL 33

Expert Comment

ID: 38357002
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

LVL 1

Author Comment

ID: 38357982
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

LVL 33

Expert Comment

ID: 38358631
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

LVL 33

Expert Comment

ID: 38358635
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

LVL 1

Author Comment

ID: 38359050
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

LVL 1

Author Comment

ID: 38359855
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

LVL 33

Accepted Solution

Robberbaron (robr) earned 2000 total points
ID: 38360758
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

LVL 1

Author Comment

ID: 38362038
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

LVL 33

Expert Comment

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

LVL 33

Expert Comment

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

LVL 1

Author Closing Comment

ID: 38362769
Thank you for all your help! Much appreciated!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.