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
228	Campaign: NAPA 2017 Upfront Digital Audio Streaming (ID: 135173)
229	Contract ID: 1292817 Advertiser: NAPA AUTO PARTS (ID: 12147)
230	In-Stream Ad Name	Ad Description	Ad Cart Id	Station	Station DMA		Flights	Imp	Plays	Clicks	CTR(%)	Revenue
231	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	59,415	65.1	N/A	N/A	199.99
232	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	116,735	102.0	N/A	N/A	392.93
233	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	120,092	126.2	N/A	N/A	404.23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
204	Contract ID: 1472457 Advertiser: LA QUINTA INNS (ID: 21207)
205	In-Stream Ad Name	Ad Description	Ad Cart Id	Station	Station DMA		Flights	Imp	Plays	Clicks	CTR(%)	Revenue
206	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	349,853	392.5	N/A	N/A	1,195.45
207	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	117,239	87.0	N/A	N/A	400.61
208	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	279,387	373.6	N/A	N/A	954.67
209	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	112,775	103.6	N/A	N/A	385.35
210	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	3,752,390	466.0	N/A	N/A	12,821.92
211	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	1,303,978	118.5	N/A	N/A	4,455.69
212	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	262,996	364.2	N/A	N/A	898.66
213	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	111,336	103.8	N/A	N/A	380.44
214	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	11,222	255.8	N/A	N/A	38.35
215	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	373	36.5	N/A	N/A	1.27
216	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	32,394	70.1	N/A	N/A	110.69
217	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	7,173	16.3	N/A	N/A	24.51
218	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	276	197.8	N/A	N/A	0.94
219	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	124	58.3	N/A	N/A	0.42
220	In-Stream Media Summary							6,341,516	2,644	N/A	N/A	21,668.96

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
16	Contract ID: 1308657 Advertiser: N/A (ID: N/A)
17	Targeted Ad Name	Ad Description	Ad Cart Id	Station	Station DMA		Flights	Imp	Plays	Clicks	CTR(%)	Revenue
18	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	23,784	0	N/A	N/A	242.60
19	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	1,773	0	N/A	N/A	18.08
20	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	35,381	0	N/A	N/A	360.89
21	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	1,171	0	N/A	N/A	11.94
22	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	52,910	0	N/A	N/A	539.68
23	Targeted Media Summary							115,019	0	N/A	N/A	1,173.19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
9
10		Contract	Rev	DFP Order	Start Date	End Date	Proposal Type	Bill Source	Currency	Billing Co	Contract Amount	Contract Impressions	Contract Delv Imps	Mock Company	PODCAST	DIFFERENCE
11		808017	4	480843417	04/01/2017	06/04/2017	Revenue	Actuals	U.S. Dollar	Mock Company	21,862.00	1,399,133	38,057	38,057		0
12		1162857	2	465729657	03/27/2017	06/18/2017	Revenue - Radio	Actuals	U.S. Dollar	Mock Company	31,149.00	7,240,239	930,182	745,593	184,589	0
13		1163097	1	465729177	06/26/2017	09/17/2017	Revenue - Radio	Actuals	U.S. Dollar	Mock Company	31,149.00	7,240,239	916,840	772,807	144,033	0
14		1168377	11	481555737	01/01/2017	12/31/2017	Revenue	Actuals	U.S. Dollar	Mock Company	334,750.00	41,512,233	162,061		162,061	0

<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>
</tbody>

Digital Contract & Delivery Aud​

Worksheet Formulas Cell Formula P11 =+M11-N11-O11 O12 =+UnCast!H872 P12 =+M12-N12-O12 O13 =+UnCast!H1874 P13 =+M13-N13-O13 O14 =+UnCast!H560 P14 =+M14-N14-O14 N11 =+'Mock Company'!H20 N12 =+'Mock Company'!H409 N13 =+'Mock Company'!H1008 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!