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

[scoobydoo]

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 Number	Customer Name	Date of Service	Month	Year	Service Description	Service Cost	Tips	Total Costs	Service Provider	Payment Method	Notes
5	Bobby	2/2/2023	February	2023	GENTLEMAN'S HAIRCUT	$19.00		$19.00	Hair Stylist - Savanah	VENMO
1	Adam	2/2/2023	February	2023	SHAMPOO & SET	$28.00	$2.00	$30.00	Hair Stylist - Savanah	INVOICED
2	Archer	2/15/2023	February	2023	NECK OR BANG TRIM	$10.00		$10.00	Hair Stylist - Savanah	INVOICED	Bang Only
4	Helen	2/15/2023	February	2023	HAIR COLOR/SHAMPOO & SET	$75.00		$75.00	Hair Stylist - Savanah	ZELLE
10	Douglas	2/12/2023	February	2023	HAIR COLOR/SHAMPOO & SET	$75.00		$75.00	Hair Stylist - Savanah	VENMO
8	Burch	2/12/2023	February	2023	HAIR COLOR/SHAMPOO & SET	$75.00		$75.00	Hair Stylist - Savanah	LOCKBOX
3	Betty	2/23/2023	February	2023	SHAMPOO & SET	$28.00	$12.00	$40.00	Hair Stylist - Savanah	INVOICED
9	Corchran	2/23/2023	February	2023	1/2 HAIR CUT	$14.00		$14.00	Hair Stylist - Savanah	VENMO
5	Bobby	2/5/2023	February	2023	MANICURE	$20.00		$20.00	Nail Technician - Faye	INVOICED
1	Adam	2/28/2023	February	2023	MANICURE	$20.00		$20.00	Nail Technician - Faye	INVOICED
5	Bobby	3/2/2023	March	2023	SHAMPOO & SET	$28.00		$28.00	Hair Stylist - Savanah	INVOICED
1	Adam	3/2/2023	March	2023	SHAMPOO & SET	$28.00		$28.00	Hair Stylist - Savanah	INVOICED

Examples of Invoices based on Data Set

Example #1

Invoice	112
Name	Adam
Customer Number	1
Service Start Date	2/1/2023
Service End Date	2/28/2023
Invoice Date	3/13/2023
Invoice To:
Adam
123 Happy Lane
Denver, CO 11122
999-999-9999
Date of Service	Service Description	Service Costs	Tips	Total	Notes
2/2/2023	SHAMPOO & SET	$28.00	$2.00	$30.00
2/28/2023	MANICURE	$20.00		$20.00
Total Due		$48.00	$2.00	$50.00

Example #2

Invoice	113
Name	Archer
Customer Number	2
Service Start Date	2/1/2023
Service End Date	2/28/2023
Invoice Date	3/13/2023
Invoice To:
Archer
456 Joy Road
Denver, CO 11122
999-999-1010
Date of Service	Service Description	Service Costs	Tips	Total	Notes
2/15/2023	NECK OR BANG TRIM	$10.00		$10.00	Bang Only
Total Due		$10.00		$10.00

Example #3

Invoice	114
Name	Betty
Customer Number	3
Service Start Date	2/1/2023
Service End Date	2/28/2023
Invoice Date	3/13/2023
Invoice To:
Betty
789 Meadow View
Denver, CO 11122
999-999-1111
Date of Service	Service Description	Service Costs	Tips	Total	Notes
2/23/2023	NECK OR BANG TRIM	$28.00	$12.00	$40.00
Total Due		$28.00	$12.00	$40.00

Example #4

Invoice	115
Name	Bobby
Customer Number	5
Service Start Date	2/1/2023
Service End Date	2/28/2023
Invoice Date	3/13/2023
Invoice To:
Bobby
1011 Everest Blvd
Denver, CO 11122
999-999-1213
Date of Service	Service Description	Service Costs	Tips	Total	Notes
2/5/2023	MANICURE	$20.00		$20.00
Total Due		$20.00		$20.00

 

[mumps]

Your data sample contains information for both February and March. How do you determine which month to use? Also, where does the Invoice Date come from?

 

[shinigamilight]

Which version of excel you're using? Looks like filter function can do this.

 

[scoobydoo]

Your data sample contains information for both February and March. How do you determine which month to use? Also, where does the Invoice Date come from?

Hello Mumps! Yes, that is correct. My data sample is showing the running log that will be kept from 2/1/2023 until forever. I've set the invoice to equal today's date (date created). I guess if it will make life easier, I could possible do a log for each month/year.

Thanks for taking time to reply.

 

[scoobydoo]

Which version of excel you're using? Looks like filter function can do this.

Hello Shinigamilight! Unfortunately, I have a very old computer and using excel 2007. Thanks for taking time to respond!

 

[mumps]

I could possible do a log for each month/year.

This would probably be the best approach. Will you ever have data for more than one year? The macro could prompt you to enter the desired month and year if that works better for you.

 

[Fluff]

I have a very old computer and using excel 2007

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[scoobydoo]

Hello Mumps! Yes, that is correct. My data sample is showing the running log that will be kept from 2/1/2023 until forever. I've set the invoice to equal today's date (date created). I guess if it will make life easier, I could possible do a log for each month/year.

Thanks for taking time to reply.

I also failed to say the invoice contains a drop down in the Customer Name Field. When I select a customer's name, it automatically fills the the Invoice To: section of the invoice.

The struggle I'm having is based on the customer name, I want it to auto populate the services, costs, tips and notes into the invoice details. It may one line or multiple lines, depending on the number of visits and services they received during a month.

 

[mumps]

Do you want to be prompted to enter the year and month? Will you be entering the address and phone number manually into the invoice?

 

[scoobydoo]

Do you want to be prompted to enter the year and month? Will you be entering the address and phone number manually into the invoice?

Yes, I would like to be prompted to enter the year and month. I did a vlookup using the customer name to populate the customer contact information.