Advanced Lookup Formula or Macro

[LearnVBA83]

Hi all,

I'm having trouble trying to automate a manual process by which I need to compare data from 3 separate reports using a contract ID as the unique identifier. The problem is two of the reports have the Contract ID with other data (Figure 1) merged in Columns K-L. Also, the amount I need to return is the total amount which for that Contract ID could be multiple rows (Figure 2). I do think the first report always has the row of the total amount I need labeled as " In-Stream Media Summary" and the other report has it labeled as "Targeted Media Summary" . The totals are also always in column H.

Figure 1 - Contract ID is merged with other words/numbers in A229 to L229. I know it doesn't looked merged using the HTML snipit below but they are (Worksheet Tab is called Mock Company)

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Campaign: NAPA 2017 Upfront Digital Audio Streaming (ID: 135173)
Contract ID: 1292817 Advertiser: NAPA AUTO PARTS (ID: 12147)
5.29-6.4 Stream	:30/:60 audio streaming ad can be accompanied by an adjacent 300x250 banner	18152817	WEPN-FM	New York NY	05/29/17-06/04/17
6.5-6.11 Stream	:30/:60 audio streaming ad can be accompanied by an adjacent 300x250 banner	18151137	WEPN-FM	New York NY	06/05/17-06/11/17
6.12-6.18 Stream	:30/:60 audio streaming ad can be accompanied by an adjacent 300x250 banner	18152937	WEPN-FM	New York NY	06/12/17-06/18/17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]228[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]229[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]230[/TD]
[TD="bgcolor: #C0C0C0, align: center"]In-Stream Ad Name[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ad Description[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ad Cart Id[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Station[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Station DMA[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]Flights[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Imp[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Plays[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Clicks[/TD]
[TD="bgcolor: #C0C0C0, align: center"]CTR(%)[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Revenue[/TD]

[TD="align: center"]231[/TD]

[TD="align: right"][/TD]

[TD="align: right"]59,415[/TD]
[TD="align: right"]65.1[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]199.99[/TD]

[TD="align: center"]232[/TD]

[TD="align: right"][/TD]

[TD="align: right"]116,735[/TD]
[TD="align: right"]102.0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]392.93[/TD]

[TD="align: center"]233[/TD]

[TD="align: right"][/TD]

[TD="align: right"]120,092[/TD]
[TD="align: right"]126.2[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]404.23[/TD]

</tbody>

Mock Company​

Figure 2 - The number of rows that make up the totals in Column H that I need to return with the lookup varies by Contract. A ungique identifier could be "In-Stream Media Summary" (merged cells A220 - G220) for the Mock Company report.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Contract ID: 1472457 Advertiser: LA QUINTA INNS (ID: 21207)
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	WEPN-FM	New York NY	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	WEPN-FM	New York NY	06/26/17-07/02/17
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	WMVP-AM	Chicago IL	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	WMVP-AM	Chicago IL	06/26/17-07/02/17
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	Mock CompanyRadioNetwork	Hartford & New Haven CT	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	Mock CompanyRadioNetwork	Hartford & New Haven CT	06/26/17-07/02/17
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	KSPN-AM	Los Angeles CA	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	KSPN-AM	Los Angeles CA	06/26/17-07/02/17
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	SideChannel1	No/Unrated Market	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	SideChannel1	No/Unrated Market	06/26/17-07/02/17
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	PaulFinebaum	No/Unrated Market	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	PaulFinebaum	No/Unrated Market	06/26/17-07/02/17
6.5-6.25 Stream	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	28419777	SideChannel2	No/Unrated Market	06/05/17-06/25/17
6.26-7.2	Targeted Impression: Impression based opportunity sold to multiple advertisers at various impression levels and flights. This :30/:60 audio streaming is NOT AD SERVED.This :30/:60 audio streaming ad is accompanied by an adjacent 300x250 and is not served by DART.	22827297	SideChannel2	No/Unrated Market	06/26/17-07/02/17
In-Stream Media Summary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]204[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]205[/TD]
[TD="bgcolor: #C0C0C0, align: center"]In-Stream Ad Name[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ad Description[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ad Cart Id[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Station[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Station DMA[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]Flights[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Imp[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Plays[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Clicks[/TD]
[TD="bgcolor: #C0C0C0, align: center"]CTR(%)[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Revenue[/TD]

[TD="align: center"]206[/TD]

[TD="align: right"][/TD]

[TD="align: right"]349,853[/TD]
[TD="align: right"]392.5[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]1,195.45[/TD]

[TD="align: center"]207[/TD]

[TD="align: right"][/TD]

[TD="align: right"]117,239[/TD]
[TD="align: right"]87.0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]400.61[/TD]

[TD="align: center"]208[/TD]

[TD="align: right"][/TD]

[TD="align: right"]279,387[/TD]
[TD="align: right"]373.6[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]954.67[/TD]

[TD="align: center"]209[/TD]

[TD="align: right"][/TD]

[TD="align: right"]112,775[/TD]
[TD="align: right"]103.6[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]385.35[/TD]

[TD="align: center"]210[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3,752,390[/TD]
[TD="align: right"]466.0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]12,821.92[/TD]

[TD="align: center"]211[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1,303,978[/TD]
[TD="align: right"]118.5[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]4,455.69[/TD]

[TD="align: center"]212[/TD]

[TD="align: right"][/TD]

[TD="align: right"]262,996[/TD]
[TD="align: right"]364.2[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]898.66[/TD]

[TD="align: center"]213[/TD]

[TD="align: right"][/TD]

[TD="align: right"]111,336[/TD]
[TD="align: right"]103.8[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]380.44[/TD]

[TD="align: center"]214[/TD]

[TD="align: right"][/TD]

[TD="align: right"]11,222[/TD]
[TD="align: right"]255.8[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]38.35[/TD]

[TD="align: center"]215[/TD]

[TD="align: right"][/TD]

[TD="align: right"]373[/TD]
[TD="align: right"]36.5[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]1.27[/TD]

[TD="align: center"]216[/TD]

[TD="align: right"][/TD]

[TD="align: right"]32,394[/TD]
[TD="align: right"]70.1[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]110.69[/TD]

[TD="align: center"]217[/TD]

[TD="align: right"][/TD]

[TD="align: right"]7,173[/TD]
[TD="align: right"]16.3[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]24.51[/TD]

[TD="align: center"]218[/TD]

[TD="align: right"][/TD]

[TD="align: right"]276[/TD]
[TD="align: right"]197.8[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]0.94[/TD]

[TD="align: center"]219[/TD]

[TD="align: right"][/TD]

[TD="align: right"]124[/TD]
[TD="align: right"]58.3[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]0.42[/TD]

[TD="align: center"]220[/TD]

[TD="align: right"]6,341,516[/TD]
[TD="align: right"]2,644[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]21,668.96[/TD]

</tbody>

Mock Company​

Figure 3 is the other report in worksheet tab called UnCast. It's the same basic report except the totals are called "Targeted Media Summary" merged in A23 - G23

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Contract ID: 1308657 Advertiser: N/A (ID: N/A)
6.1-6.30 Pre-Roll - Baseball Tonight UnCast Rotational_3	This UnCast ad is NOT AD SERVED by DART.	19742937	PodCenter: Baseball Tonight	No/Unrated Market	06/01/17-06/30/17
7.1-7.31 Pre-Roll - Baseball Tonight UnCast Rotational_4	This UnCast ad is NOT AD SERVED by DART.	19743057	PodCenter: Baseball Tonight	No/Unrated Market	07/01/17-07/31/17
6.1-6.30 Pre-Roll - Fantasy Focus Baseball UnCast_2	This UnCast ad is NOT AD SERVED by DART.	19743777	PodCenter: Fantasy Focus Baseball	No/Unrated Market	06/01/17-06/30/17
7.1-7.31 Pre-Roll - Fantasy Focus Baseball UnCast_3	This UnCast ad is NOT AD SERVED by DART.	19743897	PodCenter: Fantasy Focus Baseball	No/Unrated Market	07/01/17-07/31/17
6.5-6.30 Pre-Roll - The Lowe Post UnCast Rotational (copy 2) (copy 2)	This UnCast ad is NOT AD SERVED by DART.	19668417	PodCenter: The Lowe Post	No/Unrated Market	06/05/17-06/30/17
Targeted Media Summary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Targeted Ad Name[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ad Description[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ad Cart Id[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Station[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Station DMA[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]Flights[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Imp[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Plays[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Clicks[/TD]
[TD="bgcolor: #C0C0C0, align: center"]CTR(%)[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Revenue[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: right"]23,784[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]242.60[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1,773[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]18.08[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

[TD="align: right"]35,381[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]360.89[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1,171[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]11.94[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: right"]52,910[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]539.68[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]115,019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]N/A[/TD]
[TD="align: right"]1,173.19[/TD]

</tbody>

UnCast​

Figure 4 is the report i'm working from that I want to input the lookup or macro in rows N, O, P. Column B is the originating lookup contract ID.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Contract	Rev	DFP Order	Contract Amount	Contract Impressions	Contract Delv Imps	Mock Company	PODCAST	DIFFERENCE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFFFF"]Start Date[/TD]
[TD="bgcolor: #FFFFFF"]End Date[/TD]
[TD="bgcolor: #FFFFFF"]Proposal Type[/TD]
[TD="bgcolor: #FFFFFF"]Bill Source[/TD]
[TD="bgcolor: #FFFFFF"]Currency[/TD]
[TD="bgcolor: #FFFFFF"]Billing Co[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]808017[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]480843417[/TD]
[TD="bgcolor: #FFFFFF"]04/01/2017[/TD]
[TD="bgcolor: #FFFFFF"]06/04/2017[/TD]
[TD="bgcolor: #FFFFFF"]Revenue[/TD]
[TD="bgcolor: #FFFFFF"]Actuals[/TD]
[TD="bgcolor: #FFFFFF"]U.S. Dollar[/TD]
[TD="bgcolor: #FFFFFF"]Mock Company[/TD]
[TD="bgcolor: #FFFFFF, align: right"]21,862.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1,399,133[/TD]
[TD="bgcolor: #FFFFFF, align: right"]38,057[/TD]
[TD="align: right"]38,057[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]1162857[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]465729657[/TD]
[TD="bgcolor: #FFFFFF"]03/27/2017[/TD]
[TD="bgcolor: #FFFFFF"]06/18/2017[/TD]
[TD="bgcolor: #FFFFFF"]Revenue - Radio[/TD]
[TD="bgcolor: #FFFFFF"]Actuals[/TD]
[TD="bgcolor: #FFFFFF"]U.S. Dollar[/TD]
[TD="bgcolor: #FFFFFF"]Mock Company[/TD]
[TD="bgcolor: #FFFFFF, align: right"]31,149.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7,240,239[/TD]
[TD="bgcolor: #FFFFFF, align: right"]930,182[/TD]
[TD="align: right"]745,593[/TD]
[TD="align: right"]184,589[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]1163097[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]465729177[/TD]
[TD="bgcolor: #FFFFFF"]06/26/2017[/TD]
[TD="bgcolor: #FFFFFF"]09/17/2017[/TD]
[TD="bgcolor: #FFFFFF"]Revenue - Radio[/TD]
[TD="bgcolor: #FFFFFF"]Actuals[/TD]
[TD="bgcolor: #FFFFFF"]U.S. Dollar[/TD]
[TD="bgcolor: #FFFFFF"]Mock Company[/TD]
[TD="bgcolor: #FFFFFF, align: right"]31,149.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7,240,239[/TD]
[TD="bgcolor: #FFFFFF, align: right"]916,840[/TD]
[TD="align: right"]772,807[/TD]
[TD="align: right"]144,033[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]1168377[/TD]
[TD="bgcolor: #FFFFFF, align: right"]11[/TD]
[TD="bgcolor: #FFFFFF, align: right"]481555737[/TD]
[TD="bgcolor: #FFFFFF"]01/01/2017[/TD]
[TD="bgcolor: #FFFFFF"]12/31/2017[/TD]
[TD="bgcolor: #FFFFFF"]Revenue[/TD]
[TD="bgcolor: #FFFFFF"]Actuals[/TD]
[TD="bgcolor: #FFFFFF"]U.S. Dollar[/TD]
[TD="bgcolor: #FFFFFF"]Mock Company[/TD]
[TD="bgcolor: #FFFFFF, align: right"]334,750.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]41,512,233[/TD]
[TD="bgcolor: #FFFFFF, align: right"]162,061[/TD]
[TD="align: right"][/TD]
[TD="align: right"]162,061[/TD]
[TD="align: right"]0[/TD]

</tbody>

Digital Contract & Delivery Aud​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P11[/TH]
[TD="align: left"]=+M11-N11-O11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O12[/TH]
[TD="align: left"]=+UnCast!H872[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P12[/TH]
[TD="align: left"]=+M12-N12-O12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O13[/TH]
[TD="align: left"]=+UnCast!H1874[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P13[/TH]
[TD="align: left"]=+M13-N13-O13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O14[/TH]
[TD="align: left"]=+UnCast!H560[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P14[/TH]
[TD="align: left"]=+M14-N14-O14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N11[/TH]
[TD="align: left"]=+'Mock Company'!H20[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N12[/TH]
[TD="align: left"]=+'Mock Company'!H409[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N13[/TH]
[TD="align: left"]=+'Mock Company'!H1008[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Note: The html screenshots are just examples. The first report is usually less that 100 lines (contract ID's), but the two reports i'm comparing to (Figure 1,2, and 3) have all of the details so they all have several thousand lines. Right now i'm having to look at a contract and use control find to match the contract and manually input the amount.

Please let me know if you have any suggestions or solutions!