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
11
Medium Priority
?
416 Views
Last Modified: 2012-09-04
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
Comment
Question by:Lawrence Salvucci
  • 6
  • 5
11 Comments
 
LVL 33

Expert Comment

by:Robberbaron (robr)
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
                        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
 
LVL 1

Author Comment

by:Lawrence Salvucci
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

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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 33

Expert Comment

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

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
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

by:Lawrence Salvucci
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

by:
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
                        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
 
LVL 1

Author Comment

by:Lawrence Salvucci
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

by:Robberbaron (robr)
ID: 38362093
Just run the macro with the desired file active. That's what activesheet refers to.
0
 
LVL 33

Expert Comment

by:Robberbaron (robr)
ID: 38362099
This assumes that the orders start at a2 and E5 in s all sheets .
0
 
LVL 1

Author Closing Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question