Need help extracting groups of data with repetitive reference

[texaschai]

I am not sure how to even word this title, so if someone comes up with a better Title example, please let me know and I'll edit it.

I'm a semi-advanced Excel user in that I have taught some Excel classes (on shortcuts, pivot tables, excel formulas - basic and intermediate) but I'm not so advanced that I am super familiar or comfortable using Macros (except a simple one to create a keyboard shortcut, and even then I have to Enable Macros each time I open Excel) nor am I familiar with VBA. I think this is probably just a complex formula question, but I'm stuck so I'm hoping someone can help me.

Our company's accounting software does not create AR Customer Statements correctly, so I found out that the clerks are manually typing them line by line into an Excel template and then printing them to PDF and mailing them (60-75 customers a month x 2-8 invoices each x manual calculations DAYS of work). I figured I could expedite this a bit, and I started by exporting the current report of past due invoices (with columns for "Customer Name", "Invoice Number", "Invoice Date", "Invoice Due Date", "Terms", etc.) in one Worksheet (a tab called "Revised Aged AR") and I also created a new Template Statement in a tab called "Template". Then, I took the list of all the customers on the Revise Aged AR tab and copied them into a tab called "Current AR Customers" (removing all duplicates using the Remove Duplicates command). I then created a Macro (with the help of forums like this) that copies the Template tab and creates a new tab with the name of each customer, so that the template is duplicated 60 times except the name of the current customers receiving statements are the tab names. I also put in a formula where the customer's name would appear on the statement to change the cell containing the customer's name to match the name of the tab. Here is the formula I used for that: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255).

So far, so good. I can use Vlookups from another tab containing Customer Names and Addresses to fill in the address cells on each customer's statement, but what I am trying to do that isn't working correctly is to pull just that customer's lines of data off the Revised Aged AR tab and have it populate the statement. For example, let's say the data on the Revised Aged AR tab looks like this:

Customer Name	Invoice #	Inv Date	Terms	Due Date	Amount
All World Roofing	4348	06/28/13	Net 120	10/26/3	989.85
All World Roofing	4552	11/27/13	Net 120	03/27/14	10,500.00
Appliance King	4685	01/31/14	Net 30	03/02/14	15,187.50
Appliance King	4686	01/31/14	Net 30	03/02/14	150.00
Atlas Plumbing	4643	01/16/14	Net 30	02/14/14	473.92
Atlas Plumbing	4658	01/22/14	Net 30	02/21/14	494.65
Atlas Plumbing	4662	01/23/14	Net 30	02/22/14	2,123.90

<tbody>
</tbody>

And what I want to show up on Appliance King's statement is found in the data on their two lines of the report (namely, invoices 4685 and 4686). I have, on the template statement, room for 8 invoices, so I wrote some =ISNA formulas to give a blank row for, say the 6 extra rows in this example. However, what is happening with my Vlookup is that it is bringing over the data from Appliance King's first invoice 5 times, and then on the 6th row, it brings over their second invoice. So, my statement ends up looking like this:

Invoice #	Inv Date	Terms	Due Date	Days Late	Inv Amt	Credits	Inv Balance	Runnng Bal
4685	01/31/14	Net 30	03/02/14	10	15,187.50	0.00	15,187.50	15,187.50
4685	01/31/14	Net 30	03/02/14	10	15,187.50	0.00	15,187.50	30,750.00
4685	01/31/14	Net 30	03/02/14	10	15,187.50	0.00	15,187.50	45,562.50
4685	01/31/14	Net 30	03/02/14	10	15,187.50	0.00	15,187.50	60,750.00
4685	01/31/14	Net 30	03/02/14	10	15,187.50	0.00	15,187.50	75,937.50
4686	01/31/14	Net 30	03/02/14	10	150.00	0.00	150.00	76,087.50

<tbody>
</tbody>

So what do I need to do to change my formula so that it pulls the two invoice numbers/amounts/due dates for this customer, leaves the last 6 rows of the 8 rows blank, and doesn't repeat the first invoice 5 times?





My formula for the invoice number in the second table is: =IF(ISNA(VLOOKUP($R$6,'Revised Aged AR'!C2:E194,3,FALSE)),"",VLOOKUP($R$6,'Revised Aged AR'!C2:E194,3,FALSE)) - where R6 is the Customer Name, C2:E194 is the range of data of all customer's outstanding AR, and 3 is the column (Col E) where the invoice number is referenced on the Revised Aged AR tab. Initially, I thought this would work if I didn't "F4" the C2:E194 so that as it got copied to the next row, it would copy the next invoice referenced on the Aged AR tab (eg C3:E195). But that's illogical now that I think of it. Ideally, the C2:E194 should be $C$2:$E$194 in whatever formula works, of course. This is just how I happen to have it now.

My other formulas are basically the same, except I am doing a Vlookup of the invoice number (since those aren't duplicated) to get the invoice date, due date, terms, etc.

Lastly, I also need help with a formula that sums the invoice amounts ONLY if they fall within a certain "group" bracket (Current, >30 Days, >60 Days, >90 Days and >120 Days Past Due). I have cells at the bottom of the template that will have these group totals. So if the days late is 55 on one invoice, 59 on another, and 99 on a third, how do I get the sum to only count the two first invoices for the >30 total and the second invoice only for the 90 total?

I love this forum and already learn so much from it; hopefully I'll be able to contribute as a "problem solver" rather than a "question asker" someday!

Thanks!
Charlotte

PS No idea what tags to put here, either!

 

[CharlesChuckieCharles]

Texas

So you need to extract into your template a unique set of Invoices for a Customer after that your Vlookups should work


This code goes through the workbook, looking up invoice numbers based on worksheet name and placing them in column A from row one onwards
(sheet1 would be your "Revised Aged AR")

Sub UniqueInvoiceNoPlacement()
Dim MyWorkSheet As Worksheet
Dim X As Long
Dim Y As Long
With Sheets("Sheet1")
InvoiceDepth = .Cells(.Rows.Count, 1).End(xlUp).Row
    For Each MyWorkSheet In ActiveWorkbook.Sheets
       For X = 1 To InvoiceDepth
          If UCase(.Cells(X, 1).Value) = UCase(MyWorkSheet.Name) Then
            Y = Y + 1
            MyWorkSheet.Cells(Y, 1) = .Cells(X, 2).Value
          End If
        Next X
    Y = 0
    Next MyWorkSheet
    
End With
End Sub

for the aging stuff check out LOOKUP =========== LOOKUP(DAysLate,{30,60,90,999999999},{"Cur","30D","60","90+"})
effectively your would have in its own column (J?) against the invoice on the template

=SUMIF(J1:J100, "30D", E1:E100])

 

[texaschai]

Hi there and thank you for your reply.
I thought I might have included TOO much detail, so I'll summarize, as unfortunately, the VBA you gave me did not work.

If I have a table like this:

Col A	Col B
ABC Co	5
ABC Co	7
ABC Co	10
BCD Co	11
BCD Co	14
CDE Co	20

<colgroup><col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;"> <tbody> </tbody>

And on a second worksheet, I want to lookup all of just ABC Co's reference numbers (5, 7 and 10, in three rows just like it appears on the first worksheet), I can write a VLookup which brings me the FIRST instance in column B for "ABC Co", and returns the value of 5. But how do I then tell it on the row below the first Vlookup, when the Vlookup formula is copied down, to return the NEXT row's value in Column B for "ABC Co", thus returning a value of 7 instead of it continuing to only find the 5?

I want the Vlookup in a table to JUST return ABC Co's three numbers in Col B and then know to stop when it gets t BCD Co's rows.

Is there a formula and not a VBA application I can run?

Thanks!
Charlotte