Reconciling large amounts of data when Vlookup and Match/Index are not ideal.

[ShadowSaxx]

I need a little coaching on big data.
I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.
I have 1 Item Code.
I have a around a hundred invoices. (Sample 100)
I have a few hundred different customers. (Sample 100)
I have several hundred lines of billing data. (Sample 650)
I have several thousand lines of production data. (Sample 40,000)
In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.
In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.

Example
Prod System:
30 lines of data has a run amount and a total run amount for each line.

I am looking for tools to help reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.
Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.

Any advice?

Invoice number	Billing item	Est. Quantity	Actual quantity	Billable Qty	Sales order	Position number	Sequence	Agreement	Billing source	Description	Total Billable Qty
234672	BI_B2E01	3356	3356	5208	3	1	GKBSTMT	1	3356	B & W	5208
234672	BI_B2E01	184	184	5208	8	1	GKBSTMT	1	184	B & W	5208
234672	BI_B2E01	1668	1668	5208	3	1	GKBSTMT	1	1668	B & W	5208
234441	BI_B2E01	525	525	2366	3	5	THOCRPT	1	525	B & W	2366
234441	BI_B2E01	36	36	2366	6	5	THOCRPT	1	36	B & W	2366
234441	BI_B2E01	35	35	35	3	5	THOTIN9	1	35	B & W	35
INVOICE DATE	CUST ID	CUSTOMER NAME	INVOICE	UOM	PRICE	Sum of QTY	Sum of AMT
10/28/2013	905	Sample 1	234672	EA	$0.0522	5208	$271.62
10/28/2013	2995	Sample 2	234441	EA	$0.158908	2401	$381.54

<colgroup><col style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;" width="105"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" width="78"> <col style="width: 134pt; mso-width-source: userset; mso-width-alt: 6546;" width="179"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;" width="102"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;" width="111"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <col style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;" width="133"> <col style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;" width="114"> <tbody>
</tbody>

 

[oldbrewer]

Invoice number	Billing item	Est. Quan	Act quan	Billable Qty	Sales order	Pos number	Sequence	Agreement	Billing source	Description	Total Billable Qty
234672	BI_B2E01	3356	3356	5208	3	1	GKBSTMT	1	3356	B & W	5208
234672	BI_B2E01	184	184	5208	8	1	GKBSTMT	1	184	B & W	5208
234672	BI_B2E01	1668	1668	5208	3	1	GKBSTMT	1	1668	B & W	5208
234441	BI_B2E01	525	525	2366	3	5	THOCRPT	1	525	B & W	2366
234441	BI_B2E01	36	36	2366	6	5	THOCRPT	1	36	B & W	2366
234441	BI_B2E01	35	35	35	3	5	THOTIN9	1	35	B & W	35
INVOICE DATE	CUST ID	CUSTOMER NAME	INVOICE	UOM	PRICE	Sum of QTY	Sum of AMT
10/28/2013	905	Sample 1	234672	EA	$0.0522	5208	$271.62
10/28/2013	2995	Sample 2	234441	EA	$0.158908	2401	$381.54
invoice	price	sum of qty	sum of amount
234672	0.0522	5208	271.8576
234441	0.158908	596	94.709168
this fails because I do not understand billable quantity
why do you need to repeat 5208 three times

<colgroup><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[ShadowSaxx]

That is a column from the delimited report. Billable Qty is repeated because it aligns to the total amount intended for that Sequence. Sometimes it is the only way to reconcile the invoice between the two systems, you just have to remember to not sum the Billable Qty. For instance below the Billable Qty ties out to the Sum of Qty from the invoice:

INVOICE DATE	CUST ID	CUSTOMER NAME	INVOICE	UOM	PRICE	Sum of QTY	Sum of AMT
10/28/2013	905	Sample 1	234672	EA	$0.0522	5208	$271.62

<tbody>
</tbody>

Invoice number	Billing item	Est. Quan	Act quan	Billable Qty	Sales order	Pos number	Sequence	Agreement	Billing source	Description	Total Billable Qty
234672	BI_B2E01	3356	3356	5208	3	1	GKBSTMT	1	3356	B & W	5208
234672	BI_B2E01	184	184	5208	8	1	GKBSTMT	1	184	B & W	5208
234672	BI_B2E01	1668	1668	5208	3	1	GKBSTMT	1	1668	B & W	5208

<tbody>
</tbody>

 

[oldbrewer]

excel is not good at remembering not to do something - you need to attack the incoming data to remove this anomaly

 

[ShadowSaxx]

LOL! True. The data is not in an ideal configuration.