Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have got a table set up in one sheet with "transactions". Each row contains a name of a vendor, the amount owed or paid depending on transaction type, and the due date/transaction date. Here is some simplified sample data:

Vendor      Date    Invoice  Payment
Vendor A    6/30    $200       
Vendor A    6/30            ($200)  
Vendor B    7/5     $500    
Vendor B    7/5             ($500)
Vendor C    10/28   $50
Vendor A    10/30   $100
Vendor C    11/15   $50       

I have already built a PivotTable from that table to group these transactions by vendor and sum the remainder owed. What I'm trying to figure out is how to, for each vendor, get the next due date (min date of the group, excluding dates < Today()), or if there is no next due date then I want to see the max date for that group.

Here is what my PivotTable looks like, plus the date column I'd like to add (assuming Today() = 10/23):

Vendor      Date    Owed
Vendor B    7/5     -    
Vendor C    10/28   $100
Vendor A    10/30   $100

I know calling it next due date might not be so accurate if I end up with the date of a payment in that column, but I'm ok with that.

tl;dr : I want to find the next earliest date within each group, or the last date. How do I do this?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Well, maybe not the easiest way to approach this but I'd use VBA to build a function that returns the next due date (or largest date if next due date < Today). Array formulas are another option but they are rather inefficient.

Here is an example spreadsheet if you want to skip all the steps below :)

  1. Save your workbook as a .xlsm (Macro-enabled) file.
  2. Select all values in the Vendor column and define a named range for them (the code below assumes you've called the named range "Vendor"). I don't recommend selecting the entire column because it will cause the code to run very slowly. Just select the cells with dates; the named range will dynamically expand if you insert new rows. To be safe you can select some extra cells at the bottom; including blank cells won't hurt anything.
  3. Press ALT+F11 to open the VBA editor.
  4. From the "Insert" menu, select "Module".
  5. Paste this VBA function:

    Public Function NextByVendor(ByVal rngVendor As Range)
    
    Dim c As Range
    Dim strVendorName
    Dim lngToday As Long
    Dim lngNextDate As Long
    
    lngToday = Int(CDbl(Now()))
    lngNextDate = 0
    
    strVendorName = rngVendor.Value
    
    'Get largest date value
    For Each c In Range("Vendor")
        If c.Value = strVendorName And _
        c.Offset(0, 1).Value > lngNextDate Then
            lngNextDate = c.Offset(0, 1).Value
        End If
    Next c
    
    'If largest date is > today, get next available date
    If lngNextDate > lngToday Then
        For Each c In Range("Vendor")
            If c.Value = strVendorName And _
            c.Offset(0, 1).Value < lngNextDate And _
            c.Offset(0, 1).Value > lngToday Then
                lngNextDate = c.Offset(0, 1).Value
            End If
        Next c
    End If
    
    NextByVendor = lngNextDate
    
    End Function
    
  6. Save and close the VBA window. Return to your spreadsheet and add a new column to the right of your data. In the first data row (say it's 2), enter the formula "=NextByVendor(A2)".

  7. Copy the formula all the way down. You should end up with something like this:

    Vendor          Date            Invoice   Payment   Next Payment Due
    Vendor A        6/30/2012       $200.00             10/30/2012
    Vendor A        6/30/2012                 ($200)    10/30/2012
    Vendor B        7/5/2012        $500.00             7/5/2012
    Vendor B        7/5/2012                  ($500)    7/5/2012
    Vendor C        10/28/2012      $50.00              10/28/2012
    Vendor A        10/30/2012      $100.00             10/30/2012
    Vendor C        11/15/2012      $50.00              10/28/2012
    
  8. Refresh your PivotTable; the new data should now be available to it. You can set the value type for "Next Payment Due" in the PivotTable to "Average" or "Min".

Hope this helps!

-- jm

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.