Extract Rows of Data Using VLOOKUP With Multiple Criteria and Import to an Invoice for Each Customer

S

scoobydoo

New Member
Joined
Jan 6, 2010
Messages
25
Office Version
  1. 2007
Platform
  1. Windows
Hello, I'm seeking assistance in trying extract rows of data using vlookup with multiple criteria for a particular date range and import information into an invoice for each customers. I have a list of all salon customers and services rendered starting 2/1/2023 which I will update each month. I'm trying to create an invoice for each customers based on looking up Customer Name, Date Range for each month, i.e. 2/1/2023 thru 2/28/2023, and Payment Method = INVOICED. I would like to extract the Date of Service, Service Description, Service Costs, Tips and add Total. Please see below an example of the data set as well as the invoice.

I've worked out the part where I pull the customer's information into the invoice. However, I'm having difficulties figuring out how to get the list of services provided for a particular customer by date during the month.

Any assistance someone can provide will be greatly appreciated.

Data Set Example:

Customer NumberCustomer NameDate of ServiceMonthYearService DescriptionService CostTipsTotal CostsService ProviderPayment MethodNotes
5Bobby2/2/2023February2023GENTLEMAN'S HAIRCUT$19.00$19.00Hair Stylist - SavanahVENMO
1Adam2/2/2023February2023SHAMPOO & SET$28.00$2.00$30.00Hair Stylist - SavanahINVOICED
2Archer2/15/2023February2023NECK OR BANG TRIM$10.00$10.00Hair Stylist - SavanahINVOICEDBang Only
4Helen2/15/2023February2023HAIR COLOR/SHAMPOO & SET$75.00$75.00Hair Stylist - SavanahZELLE
10Douglas2/12/2023February2023HAIR COLOR/SHAMPOO & SET$75.00$75.00Hair Stylist - SavanahVENMO
8Burch2/12/2023February2023HAIR COLOR/SHAMPOO & SET$75.00$75.00Hair Stylist - SavanahLOCKBOX
3Betty2/23/2023February2023SHAMPOO & SET$28.00$12.00$40.00Hair Stylist - SavanahINVOICED
9Corchran2/23/2023February20231/2 HAIR CUT$14.00$14.00Hair Stylist - SavanahVENMO
5Bobby2/5/2023February2023MANICURE$20.00$20.00Nail Technician - FayeINVOICED
1Adam2/28/2023February2023MANICURE$20.00$20.00Nail Technician - FayeINVOICED
5Bobby3/2/2023March2023SHAMPOO & SET$28.00$28.00Hair Stylist - SavanahINVOICED
1Adam3/2/2023March2023SHAMPOO & SET$28.00$28.00Hair Stylist - SavanahINVOICED

Examples of Invoices based on Data Set

Example #1
Invoice112
NameAdam
Customer Number1
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Adam
123 Happy Lane
Denver, CO 11122
999-999-9999
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/2/2023SHAMPOO & SET$28.00$2.00$30.00
2/28/2023MANICURE$20.00$20.00
Total Due$48.00$2.00$50.00


Example #2
Invoice113
NameArcher
Customer Number2
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Archer
456 Joy Road
Denver, CO 11122
999-999-1010
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/15/2023NECK OR BANG TRIM$10.00$10.00Bang Only
Total Due$10.00$10.00


Example #3
Invoice114
NameBetty
Customer Number3
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Betty
789 Meadow View
Denver, CO 11122
999-999-1111
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/23/2023NECK OR BANG TRIM$28.00$12.00$40.00
Total Due$28.00$12.00$40.00


Example #4
Invoice115
NameBobby
Customer Number5
Service Start Date2/1/2023
Service End Date2/28/2023
Invoice Date3/13/2023
Invoice To:
Bobby
1011 Everest Blvd
Denver, CO 11122
999-999-1213
Date of ServiceService DescriptionService CostsTipsTotalNotes
2/5/2023MANICURE$20.00$20.00
Total Due$20.00$20.00
 
Sort by date Sort by votes
scoobydoo said:
I did a vlookup using the customer name to populate the customer contact information.
Click to expand...
Is the customer information on a different sheet. If so, please post some sample data.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Click here to download your file. In the "Invoice Template" sheet, select a name from the drop down list in B2. A new sheet will be created with the invoice for the selected name. Please do not modify the "Invoice Template" sheet. It is simply used to select the desired name. The macro could also input the customer information if it exists somewhere in your workbook.
 
Upvote 0
mumps said:
Click here to download your file. In the "Invoice Template" sheet, select a name from the drop down list in B2. A new sheet will be created with the invoice for the selected name. Please do not modify the "Invoice Template" sheet. It is simply used to select the desired name. The macro could also input the customer information if it exists somewhere in your workbook.
Click to expand...
Hello, unfortunately I didn't get a drop down list in B2. I'm not sure if it's my version of Excel. I also created a sample workbook that contains all the data tabs needed for this project. Is there some way I can send it to you?
 
Upvote 0
You could upload the file to a free file sharing site like Dropbox.com and post a link to the file here.
 
Upvote 0
This is the revised macro. I forgot to tidy it up in the file I sent you so please replace the macro in the file with the one below. I had to modify your Real Invoice Template sheet to unmerge all the merged cells. You should avoid merging cells because they almost always cause problems for macros. You could simply widen a column if you need more space or do a little research into "CenterAcrossSelection".
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C3" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim srcWS As Worksheet, CCI As Worksheet, mon As String, yr As String
    Dim fVisRow As Long, lRow As Long, lRow2 As Long, monNum As Long, rName As Range
    Set srcWS = Sheets("Data Set (Service Log)")
    Set CCI = Sheets("Customer Contact Information")
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    mon = InputBox("Enter the month name.")
    If mon = "" Then Exit Sub
    If WorksheetFunction.CountIf(srcWS.Range("D:D"), mon) = 0 Then
        MsgBox ("Invalid month.  Please try again.")
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    yr = InputBox("Enter the year.")
    If yr = "" Then Exit Sub
    If WorksheetFunction.CountIf(srcWS.Range("E:E"), yr) = 0 Then
        MsgBox ("Invalid year.  Please try again.")
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    monNum = Month(DateValue("01 " & mon & " " & yr))
    With srcWS.Range("A1").CurrentRegion
        .AutoFilter 2, Target
        .AutoFilter 4, mon
        .AutoFilter 5, yr
        If srcWS.[subtotal(103,A:A)] - 1 = 0 Then
            MsgBox ("No data exists for " & Target & " for " & mon & " " & yr & ".")
            Target.ClearContents
            .AutoFilter
            Application.EnableEvents = True
            Exit Sub
        End If
        ActiveSheet.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Target & " " & mon & "-" & yr
        fVisRow = srcWS.Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
        With ActiveSheet
            .Range("C4") = srcWS.Range("A" & fVisRow)
            .Range("C5") = DateSerial(yr, monNum, 1)
            .Range("C6") = Application.WorksheetFunction.EoMonth(srcWS.Range("C" & fVisRow), 0)
            .Range("C5:C6").NumberFormat = "mm-dd-yyyy"
            .Range("C7") = Date
            Set rName = CCI.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            .Range("B11") = rName.Offset(, 2)
            .Range("C13") = rName.Offset(, 8)
            .Range("B12").Resize(3).Value = WorksheetFunction.Transpose(Array(rName.Offset(, 3), rName.Offset(, 5), rName.Offset(, 8)))
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("C:C").SpecialCells(xlCellTypeVisible)).Copy .Range("B20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("F:I").SpecialCells(xlCellTypeVisible)).Copy .Range("D20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("L:L").SpecialCells(xlCellTypeVisible)).Copy .Range("H20")
            .Range("G40").Formula = "=sum(G20:G36)"
            .Range("G41").Formula = "=sum(F20:F36)"
            .Range("G42").Formula = "=sum(G40:G41)"
            .Columns.AutoFit
        End With
        .AutoFilter
    End With
    Target.ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
This is the revised macro. I forgot to tidy it up in the file I sent you so please replace the macro in the file with the one below. I had to modify your Real Invoice Template sheet to unmerge all the merged cells. You should avoid merging cells because they almost always cause problems for macros. You could simply widen a column if you need more space or do a little research into "CenterAcrossSelection".
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C3" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim srcWS As Worksheet, CCI As Worksheet, mon As String, yr As String
    Dim fVisRow As Long, lRow As Long, lRow2 As Long, monNum As Long, rName As Range
    Set srcWS = Sheets("Data Set (Service Log)")
    Set CCI = Sheets("Customer Contact Information")
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    mon = InputBox("Enter the month name.")
    If mon = "" Then Exit Sub
    If WorksheetFunction.CountIf(srcWS.Range("D:D"), mon) = 0 Then
        MsgBox ("Invalid month.  Please try again.")
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    yr = InputBox("Enter the year.")
    If yr = "" Then Exit Sub
    If WorksheetFunction.CountIf(srcWS.Range("E:E"), yr) = 0 Then
        MsgBox ("Invalid year.  Please try again.")
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
    monNum = Month(DateValue("01 " & mon & " " & yr))
    With srcWS.Range("A1").CurrentRegion
        .AutoFilter 2, Target
        .AutoFilter 4, mon
        .AutoFilter 5, yr
        If srcWS.[subtotal(103,A:A)] - 1 = 0 Then
            MsgBox ("No data exists for " & Target & " for " & mon & " " & yr & ".")
            Target.ClearContents
            .AutoFilter
            Application.EnableEvents = True
            Exit Sub
        End If
        ActiveSheet.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Target & " " & mon & "-" & yr
        fVisRow = srcWS.Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
        With ActiveSheet
            .Range("C4") = srcWS.Range("A" & fVisRow)
            .Range("C5") = DateSerial(yr, monNum, 1)
            .Range("C6") = Application.WorksheetFunction.EoMonth(srcWS.Range("C" & fVisRow), 0)
            .Range("C5:C6").NumberFormat = "mm-dd-yyyy"
            .Range("C7") = Date
            Set rName = CCI.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            .Range("B11") = rName.Offset(, 2)
            .Range("C13") = rName.Offset(, 8)
            .Range("B12").Resize(3).Value = WorksheetFunction.Transpose(Array(rName.Offset(, 3), rName.Offset(, 5), rName.Offset(, 8)))
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("C:C").SpecialCells(xlCellTypeVisible)).Copy .Range("B20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("F:I").SpecialCells(xlCellTypeVisible)).Copy .Range("D20")
            Intersect(srcWS.Rows("2:" & lRow), srcWS.Range("L:L").SpecialCells(xlCellTypeVisible)).Copy .Range("H20")
            .Range("G40").Formula = "=sum(G20:G36)"
            .Range("G41").Formula = "=sum(F20:F36)"
            .Range("G42").Formula = "=sum(G40:G41)"
            .Columns.AutoFit
        End With
        .AutoFilter
    End With
    Target.ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Click to expand...
Hello, Mumps! Thank you so much for your help! This works perfectly. Actually, it's better than I expected. I appreciate you adding the date entry option as well as adding the option of creating a separate invoice tab for each customer. I will also take your advice and unmerge cells to help macros run properly. I would really love to learn how to write code. This just made life a whole lot easier when doing customer billing. Thanks a million!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
populate amounts for column debit,credit for each customer from file to another
Replies
0
Views
206
KalilMe
K
S
  • Question
Need help creating a multiple tab invoice from a raw data file
Replies
1
Views
204
sreed39
S
K
  • Solved
extraction invoices detail for TOTAL row for each sheet individual
Replies
7
Views
251
RudRud
R
K
  • Question
compare data across sheets for 6000 rows for each sheet individual based on sheet name
Replies
0
Views
192
KalilMe
K
A
  • Question
compare extraction right data for information invoices with wrong data for each range
Replies
2
Views
267
abdo meghari
A

Forum statistics

Threads
1,214,832
Messages
6,121,851
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top