List Invoices w/ Description for CustID

[gtd526]

Hello,
Trying to list all invoices per CustID(K2) with each Description(E:E) on the invoice (can be multiple descriptions per invoice).
Below are the results I have so far, but its not listing correctly when I change to CustID.
The first example is how it should look when completed. When I change the CustID it changes incorrectly. See additional examples below.
See J:J for the summary, which is what I want to correct.
Thank you.

Correct:

Create Invoice.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Invoice	Cust ID	Name	Address	Description	Quantity	Subtotal	Grand Total		List of Invoices by Cust ID
2	1	gredow	Greg Downs	3090 N Course Dr #910	Tile - Installation	1	$15,000.00	$15,000.00		Cust ID:	joebla
3	2	joebla	Joe Black	123 Main St	Door Hinge	1	$100.00	$100.00		Address:	123 Main St	Correct
4	3	gredow	Greg Downs		Door Insulation	1	$200.00	$200.00
5	4	gredow	Greg Downs		Replace Tile	1	$5,000.00	$5,000.00	Count	Invoice(s)	Summary		Price
6	5	gredow	Greg Downs		Ceiling Fan	1	$356.75	$356.75	1	2	Door Hinge		$100.00
7	6	joebla	Joe Black		Install Lighting	1	$250.00	$250.00	3	6	Install Lighting		$600.00
8	6	joebla	Joe Black		Cabinets	2	$100.00	$200.00			Cabinets
9	6	joebla	Joe Black		Bed2	1	$150.00	$150.00			Bed2
10	7	gredow	Greg Downs		Floor	1	$25.00	$25.00	2	8	Walls		$720.00
11	7	gredow	Greg Downs		Ceiling	1	$300.00	$300.00			Floor Rug
12	8	joebla	Joe Black		Walls	2	$120.00	$240.00	2	9	Patch Walls		$450.00
13	8	joebla	Joe Black		Floor Rug	3	$160.00	$480.00			Thin Set Grout
14	9	joebla	Joe Black		Patch Walls	1	$200.00	$200.00	2	10	Lights		$350.00
15	9	joebla	Joe Black		Thin Set Grout	1	$250.00	$250.00			Ceiling
16	10	joebla	Joe Black		Lights	1	$300.00	$300.00	1	11	help		$25.00
17	10	joebla	Joe Black		Ceiling	2	$50.00	$100.00
18	11	joebla	Joe Black		help	1	$25.00	$25.00
19	12	jasblo	Jas Blow		Kitchen	1	$65.00	$65.00
20	13	jasblo	Jas Blow		Carpet	2	$100.00	$200.00
21	13	jasblo	Jas Blow		Blinds	1	$130.00	$130.00				Total:	$2,245.00
Test

Cell Formulas
Range	Formula
K3	K3	=IFERROR(INDEX(Details!D:D,MATCH(Details!K2,Details!B:B,0)),"")
I6:I20	I6	=IF(ISNUMBER(J6),COUNTIFS($A$1:$A$99, $J6),"")
J6:J20	J6	=IF(ISNUMBER(M6),INDEX($A:$A, MATCH(0, IF($K$2=$B:$B, COUNTIF($J$5:$J5, $A:$A), ""), 0)),"")
K6:K20	K6	=IFERROR(IF(COUNTIF($A$1:$A$99, $J6)=1,INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/($J6=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1)),INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/(LOOKUP(2,1/($J$6:J6<>""),$J$6:J6)=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1))),"")
A18:A20,A16,A14,A3:A7	A3	=A2+1
B2:B21	B2	=LOWER(LEFT(C2,3)&MID(C2,FIND(" ",C2)+1,3))
H2:H21	H2	=F2*G2
M6:M7	M6	=IFERROR(IF(COUNTIFS($A:$A, $J6,$A:$A,"<>")>=2,SUMIF(A:A,$J6,H:H),INDEX(G:G,MATCH(J6,A:A,0))),"")
M11:M20,M8:M9	M8	=IFERROR(IF(COUNTIFS($A:$A, $J8,$A:$A,"<>")>=2,SUMIF(A:A,$J8,G:G),(INDEX(G:G,MATCH(J8,A:A,0)))),"")
M10	M10	=IFERROR(IF(COUNTIFS($A:$A, $J10,$A:$A,"<>")>=2,SUMIF(A:A,$J10,H:H),INDEX(H:H,MATCH(J10,A:A,0))),"")
M21	M21	=SUM(IF(ISERROR(M6:M20),"",M6:M20))
Press CTRL+SHIFT+ENTER to enter array formulas.

Incorrect:

Create Invoice.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Invoice	Cust ID	Name	Address	Description	Quantity	Subtotal	Grand Total		List of Invoices by Cust ID
2	1	gredow	Greg Downs	3090 N Course Dr #910	Tile - Installation	1	$15,000.00	$15,000.00		Cust ID:	gredow
3	2	joebla	Joe Black	123 Main St	Door Hinge	1	$100.00	$100.00		Address:	123 Main St	Incorrect
4	3	gredow	Greg Downs		Door Insulation	1	$200.00	$200.00
5	4	gredow	Greg Downs		Replace Tile	1	$5,000.00	$5,000.00	Count	Invoice(s)	Summary		Price
6	5	gredow	Greg Downs		Ceiling Fan	1	$356.75	$356.75	1	1	Tile - Installation		$15,000.00
7	6	joebla	Joe Black		Install Lighting	1	$250.00	$250.00	1	3	Door Insulation		$200.00
8	6	joebla	Joe Black		Cabinets	2	$100.00	$200.00
9	6	joebla	Joe Black		Bed2	1	$150.00	$150.00
10	7	gredow	Greg Downs		Floor	1	$25.00	$25.00	1	4	Replace Tile		$5,000.00
11	7	gredow	Greg Downs		Ceiling	1	$300.00	$300.00
12	8	joebla	Joe Black		Walls	2	$120.00	$240.00	1	5	Ceiling Fan		$356.75
13	8	joebla	Joe Black		Floor Rug	3	$160.00	$480.00
14	9	joebla	Joe Black		Patch Walls	1	$200.00	$200.00	2	7	Floor		$325.00
15	9	joebla	Joe Black		Thin Set Grout	1	$250.00	$250.00			Ceiling
16	10	joebla	Joe Black		Lights	1	$300.00	$300.00
17	10	joebla	Joe Black		Ceiling	2	$50.00	$100.00
18	11	joebla	Joe Black		help	1	$25.00	$25.00
19	12	jasblo	Jas Blow		Kitchen	1	$65.00	$65.00
20	13	jasblo	Jas Blow		Carpet	2	$100.00	$200.00
21	13	jasblo	Jas Blow		Blinds	1	$130.00	$130.00				Total:	$20,881.75
Test

Cell Formulas
Range	Formula
K3	K3	=IFERROR(INDEX(Details!D:D,MATCH(Details!K2,Details!B:B,0)),"")
I6:I20	I6	=IF(ISNUMBER(J6),COUNTIFS($A$1:$A$99, $J6),"")
J6:J20	J6	=IF(ISNUMBER(M6),INDEX($A:$A, MATCH(0, IF($K$2=$B:$B, COUNTIF($J$5:$J5, $A:$A), ""), 0)),"")
K6:K20	K6	=IFERROR(IF(COUNTIF($A$1:$A$99, $J6)=1,INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/($J6=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1)),INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/(LOOKUP(2,1/($J$6:J6<>""),$J$6:J6)=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1))),"")
A18:A20,A16,A14,A3:A7	A3	=A2+1
B2:B21	B2	=LOWER(LEFT(C2,3)&MID(C2,FIND(" ",C2)+1,3))
H2:H21	H2	=F2*G2
M6:M7	M6	=IFERROR(IF(COUNTIFS($A:$A, $J6,$A:$A,"<>")>=2,SUMIF(A:A,$J6,H:H),INDEX(G:G,MATCH(J6,A:A,0))),"")
M11:M20,M8:M9	M8	=IFERROR(IF(COUNTIFS($A:$A, $J8,$A:$A,"<>")>=2,SUMIF(A:A,$J8,G:G),(INDEX(G:G,MATCH(J8,A:A,0)))),"")
M10	M10	=IFERROR(IF(COUNTIFS($A:$A, $J10,$A:$A,"<>")>=2,SUMIF(A:A,$J10,H:H),INDEX(H:H,MATCH(J10,A:A,0))),"")
M21	M21	=SUM(IF(ISERROR(M6:M20),"",M6:M20))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alansidman]

Using Power Query and a Parameter Look up, I have created a means for you to filter on whichever customer ID you would like. It can be found on box.net. Instructions on how to filter are in the file. You will need to create two queries and link them appropriately. Look at my signature if you are unfamiliar with Power Query

PQ Cust Look up.xlsx | Powered by Box

app.box.com

 

[gtd526]

Using Power Query and a Parameter Look up, I have created a means for you to filter on whichever customer ID you would like. It can be found on box.net. Instructions on how to filter are in the file. You will need to create two queries and link them appropriately. Look at my signature if you are unfamiliar with Power Query

PQ Cust Look up.xlsx | Powered by Box

app.box.com

the "Box.net" is asking for a monthly fee, which I'm not interested to do. Customers will be added, etc, and I what it to look like my first example.
Thank you for the reply.

 

[alansidman]

Not sure why Box.net would ask for a monthly fee unless you opted for one of the advanced features. I don't pay anything and have been using it for over 10 years. Sorry, what you are asking for requires very special VBA skills which are not in my wheelhouse. It is obviously a case of form over substance. Good Luck.