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

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
Asked:
Lawrence Salvucci
  • 6
  • 5
1 Solution
 
Robberbaron (robr)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
                        firstaddress = fnd1.Address
                        Do
                            totallbs = totallbs + fnd1.Offset(0, 2).Value
                            duedate = fnd1.Offset(0, 0).Value
                            orderno = fnd1.Offset(0, 1).Value
                            Set fnd1 = .FindNext
                        
                        Loop While Not fnd1 Is Nothing And fnd1.Address <> firstaddress
                    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
                        firstaddress = fnd1.Address
                        Do
                            totallbs = totallbs + fnd1.Offset(0, 2).Value
                            duedate = fnd1.Offset(0, 0).Value
                            orderno = fnd1.Offset(0, 1).Value
                            Set fnd1 = .FindNext
                        
                        Loop While Not fnd1 Is Nothing And fnd1.Address <> firstaddress
                    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

Open in new window

0
 
Lawrence SalvucciInformation 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
 
Robberbaron (robr)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

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Robberbaron (robr)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

Open in new window

0
 
Lawrence SalvucciInformation 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
 
Lawrence SalvucciInformation 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
 
Robberbaron (robr)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
                        firstaddress = fnd1.Address
                        Do
                            totallbs = totallbs + fnd1.Offset(0, 2).Value
                            duedate = fnd1.Offset(0, 0).Value
                            orderno = orderno & "/" & fnd1.Offset(0, 1).Value
                            Set fnd1 = .FindNext
                        
                        Loop While Not fnd1 Is Nothing And fnd1.Address <> firstaddress
                    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
                        firstaddress = fnd1.Address
                        Do
                            totallbs = totallbs + fnd1.Offset(0, 2).Value
                            duedate = fnd1.Offset(0, 0).Value
                            orderno = orderno & "/" & fnd1.Offset(0, 1).Value
                            Set fnd1 = .FindNext
                        
                        Loop While Not fnd1 Is Nothing And fnd1.Address <> firstaddress
                    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
        'already at top
        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
        'already at top
        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

Open in new window

0
 
Lawrence SalvucciInformation 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
 
Robberbaron (robr)Commented:
Just run the macro with the desired file active. That's what activesheet refers to.
0
 
Robberbaron (robr)Commented:
This assumes that the orders start at a2 and E5 in s all sheets .
0
 
Lawrence SalvucciInformation 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now