Copy sheet and add Location number

[kenpcli]

I am trying get a macro to look down column A and each change in site name add their location number underneath it.

		Chg Amt	Pay Amt	Adj Amt	Ref Amt	Bal Amt
Albuquerque
60	Totals For AETNA	$51,896.00	-$31,204.59	-$19,152.69	$164.35	$1,703.07
60	Totals For BCBS	$659,678.00	-$320,482.96	-$324,294.18	$7,687.05	$22,587.91
60	Totals For CIGNA	$264.00	-$211.20	-$52.80	$0.00	$0.00
60	Totals For COMMERCIAL	$20,122.00	-$13,354.59	-$4,563.70	$0.00	$2,203.71
60	Totals For DSHS	$8,573.00	-$4,020.36	-$60.64	$0.00	$4,492.00
60	Totals For FCHN	$3,774.00	-$2,868.21	-$905.79	$0.00	$0.00
60	Totals For HUMANA	$309.00	-$167.12	-$141.88	$0.00	$0.00
60	Totals For IPN	$3,923.00	-$2,678.76	-$1,244.24	$0.00	$0.00
60	Totals For MEDICAID COMMERCIAL	$115,651.00	-$41,467.85	-$57,352.41	$498.51	$17,329.25
60	Totals For MEDICARE	$1,928,300.00	-$849,877.14	-$1,015,188.85	$3,064.69	$66,298.70
60	Totals For MEDICARE ADVANTAGE	$440,932.00	-$175,505.21	-$210,678.60	$2,942.08	$57,690.27
60	Totals For MEDICARE RR	$37,253.00	-$15,900.45	-$20,182.52	$0.00	$1,170.03
60	Totals For MOLINA	$0.00	$0.00	$0.00	$0.00	$0.00
60	Totals For MULTIPLAN	$9,156.00	-$4,661.86	-$1,026.14	$0.00	$3,468.00
60	Totals For SELF PAY	$80,687.75	-$38,452.48	-$37,242.02	$149.40	$5,142.65
60	Totals For TRICARE	$39,590.00	-$18,544.17	-$12,423.73	$921.20	$9,543.30
60	Totals For UNITED HEALTHCARE	$237,266.00	-$153,616.60	-$65,164.26	$1,515.56	$20,000.70
60	Totals For VETERANS ADMIN	$23,748.00	-$10,461.24	-$10,419.76	$0.00	$2,867.00
Totals For Albuquerque		$3,661,122.75	-$1,683,474.79	-$1,780,094.21	$16,942.84	$214,496.59
Bellevue
30	Totals For AETNA	$504,981.00	-$312,837.07	-$173,143.99	$8,490.26	$27,490.20
30	Totals For CIGNA	$201,832.00	-$137,141.51	-$43,931.99	$546.69	$21,305.19

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

 

[Fluff]

Are the site names hard values or are they the result of formulae?
As the lookup was working originally, have you changed anything in the lookup table?

 

[kenpcli]

Are the site names hard values or are they the result of formulae?
As the lookup was working originally, have you changed anything in the lookup table?

The names are hard values and it looks at the table and fills down accordingly. I haven't changed anything on the table or sheet itself.

 

[Fluff]

Could you supply a sample of the look table, the raw data, & what result you are getting?

 

[kenpcli]

Could you supply a sample of the look table, the raw data, & what result you are getting?

Here are the images of the Legend it is looking at, the data as it appears before adding site location and what I need it to look like afterwards.

[/IMG]

[/IMG]

[/IMG][/IMG]

 

[kenpcli]

Albuquerque	60
Bellevue	30
Bellingham	35
Boise	40
Chehalis	10
Great Falls	47
Kennewick	20
Lewiston	42
Olympia	16
Portland	18
Silverdale	22
Spokane	29
Tacoma	12
Tualatin	19
Vancouver	14
Yakima	25

<colgroup><col><col></colgroup><tbody>
</tbody>

			Pacific Cataract And Laser Institute
			PCLI CONTRACTUAL YTD BY SITE
			From 1/1/2017 to 9/30/2017
10/16/2017 12:04 PM
		Chg Amt	Pay Amt	Adj Amt	Ref Amt	Bal Amt
Albuquerque
	Totals For AETNA	$51,896.00	-$31,204.59	-$19,152.69	$164.35	$1,703.07
	Totals For BCBS	$659,678.00	-$320,482.96	-$324,294.18	$7,687.05	$22,587.91
	Totals For CIGNA	$264.00	-$211.20	-$52.80	$0.00	$0.00
	Totals For COMMERCIAL	$20,122.00	-$13,354.59	-$4,563.70	$0.00	$2,203.71
	Totals For DSHS	$8,573.00	-$4,020.36	-$60.64	$0.00	$4,492.00
	Totals For FCHN	$3,774.00	-$2,868.21	-$905.79	$0.00	$0.00
	Totals For HUMANA	$309.00	-$167.12	-$141.88	$0.00	$0.00
	Totals For IPN	$3,923.00	-$2,678.76	-$1,244.24	$0.00	$0.00
	Totals For MEDICAID COMMERCIAL	$115,651.00	-$41,467.85	-$57,352.41	$498.51	$17,329.25
	Totals For MEDICARE	$1,928,300.00	-$849,877.14	-$1,015,188.85	$3,064.69	$66,298.70
	Totals For MEDICARE ADVANTAGE	$440,932.00	-$175,505.21	-$210,678.60	$2,942.08	$57,690.27
	Totals For MEDICARE RR	$37,253.00	-$15,900.45	-$20,182.52	$0.00	$1,170.03
	Totals For MOLINA	$0.00	$0.00	$0.00	$0.00	$0.00
	Totals For MULTIPLAN	$9,156.00	-$4,661.86	-$1,026.14	$0.00	$3,468.00
	Totals For SELF PAY	$80,687.75	-$38,452.48	-$37,242.02	$149.40	$5,142.65
	Totals For TRICARE	$39,590.00	-$18,544.17	-$12,423.73	$921.20	$9,543.30
	Totals For UNITED HEALTHCARE	$237,266.00	-$153,616.60	-$65,164.26	$1,515.56	$20,000.70
	Totals For VETERANS ADMIN	$23,748.00	-$10,461.24	-$10,419.76	$0.00	$2,867.00
Totals For Albuquerque		$3,661,122.75	-$1,683,474.79	-$1,780,094.21	$16,942.84	$214,496.59
Bellevue
	Totals For AETNA	$504,981.00	-$312,837.07	-$173,143.99	$8,490.26	$27,490.20
	Totals For CIGNA	$201,832.00	-$137,141.51	-$43,931.99	$546.69	$21,305.19
	Totals For COMMERCIAL	$314,544.00	-$221,865.05	-$27,915.65	$0.00	$64,763.30
	Totals For COMMERCIAL FOUNDATIONS	$11,292.00	-$3,000.00	$0.00	$0.00	$8,292.00
	Totals For CONTRACTED COMMERCIAL	$454.00	-$241.25	-$129.29	$0.00	$83.46
	Totals For COORDINATED CARE	$216,179.00	-$93,977.41	-$111,775.06	$600.01	$11,026.54
	Totals For DSHS	$70,023.00	-$21,523.32	-$38,280.68	$0.00	$10,219.00
	Totals For FCHN	$195,657.00	-$139,056.11	-$42,719.61	$1,304.50	$15,185.78
	Totals For IPN	$55.00	-$20.41	-$34.59	$0.00	$0.00
	Totals For KAISER	$6,999.00	-$5,714.67	-$1,499.94	$215.61	$0.00
	Totals For LCA	$26,400.00	-$22,140.00	-$4,060.00	$0.00	$200.00
	Totals For MEDICAID COMMERCIAL	$398,386.00	-$132,939.20	-$244,692.80	$2,443.32	$23,197.32
	Totals For MEDICARE	$3,723,687.00	-$1,819,239.29	-$1,853,265.91	$6,593.18	$57,774.98
	Totals For MEDICARE ADVANTAGE	$2,030,582.00	-$958,091.08	-$985,512.10	$3,658.84	$90,637.66
	Totals For MEDICARE RR	$7,692.00	-$4,045.10	-$3,646.90	$0.00	$0.00
	Totals For MOLINA	$62,595.00	-$26,354.32	-$35,371.54	$24.30	$893.44
	Totals For MULTIPLAN	$4,369.00	-$2,375.01	-$1,594.99	$0.00	$399.00
	Totals For NO INSURANCE	$15,433.00	-$10,156.40	-$5,276.60	$0.00	$0.00
	Totals For PREMERA	$736,026.00	-$454,644.24	-$267,642.69	$10,087.88	$23,826.95
	Totals For REGENCE	$1,240,100.00	-$824,285.00	-$358,944.56	$4,376.25	$61,246.69
	Totals For SELF PAY	$787,037.00	-$679,491.06	-$82,084.75	$2,123.00	$27,584.19
	Totals For TRICARE	$1,287.00	-$801.85	-$485.15	$0.00	$0.00
	Totals For UNITED HEALTHCARE	$235,518.00	-$161,307.83	-$60,194.42	$2,498.52	$16,514.27
Totals For Bellevue		$10,791,128.00	-$6,031,247.18	-$4,342,203.21	$42,962.36	$460,639.97
Bellingham
	Totals For AETNA	$75,382.00	-$45,593.53	-$23,321.97	$0.00	$6,466.50
	Totals For CIGNA	$36,640.00	-$26,159.20	-$9,112.80	$0.00	$1,368.00

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

			Pacific Cataract And Laser Institute
			PCLI CONTRACTUAL YTD BY SITE
			From 1/1/2017 to 9/30/2017
10/16/2017 12:04 PM
		Chg Amt	Pay Amt	Adj Amt	Ref Amt	Bal Amt
Albuquerque
60	Totals For AETNA	$51,896.00	-$31,204.59	-$19,152.69	$164.35	$1,703.07
60	Totals For BCBS	$659,678.00	-$320,482.96	-$324,294.18	$7,687.05	$22,587.91
60	Totals For CIGNA	$264.00	-$211.20	-$52.80	$0.00	$0.00
60	Totals For COMMERCIAL	$20,122.00	-$13,354.59	-$4,563.70	$0.00	$2,203.71
60	Totals For DSHS	$8,573.00	-$4,020.36	-$60.64	$0.00	$4,492.00
60	Totals For FCHN	$3,774.00	-$2,868.21	-$905.79	$0.00	$0.00
60	Totals For HUMANA	$309.00	-$167.12	-$141.88	$0.00	$0.00
60	Totals For IPN	$3,923.00	-$2,678.76	-$1,244.24	$0.00	$0.00
60	Totals For MEDICAID COMMERCIAL	$115,651.00	-$41,467.85	-$57,352.41	$498.51	$17,329.25
60	Totals For MEDICARE	$1,928,300.00	-$849,877.14	-$1,015,188.85	$3,064.69	$66,298.70
60	Totals For MEDICARE ADVANTAGE	$440,932.00	-$175,505.21	-$210,678.60	$2,942.08	$57,690.27
60	Totals For MEDICARE RR	$37,253.00	-$15,900.45	-$20,182.52	$0.00	$1,170.03
60	Totals For MOLINA	$0.00	$0.00	$0.00	$0.00	$0.00
60	Totals For MULTIPLAN	$9,156.00	-$4,661.86	-$1,026.14	$0.00	$3,468.00
60	Totals For SELF PAY	$80,687.75	-$38,452.48	-$37,242.02	$149.40	$5,142.65
60	Totals For TRICARE	$39,590.00	-$18,544.17	-$12,423.73	$921.20	$9,543.30
60	Totals For UNITED HEALTHCARE	$237,266.00	-$153,616.60	-$65,164.26	$1,515.56	$20,000.70
60	Totals For VETERANS ADMIN	$23,748.00	-$10,461.24	-$10,419.76	$0.00	$2,867.00
Totals For Albuquerque		$3,661,122.75	-$1,683,474.79	-$1,780,094.21	$16,942.84	$214,496.59
Bellevue
30	Totals For AETNA	$504,981.00	-$312,837.07	-$173,143.99	$8,490.26	$27,490.20
30	Totals For CIGNA	$201,832.00	-$137,141.51	-$43,931.99	$546.69	$21,305.19
30	Totals For COMMERCIAL	$314,544.00	-$221,865.05	-$27,915.65	$0.00	$64,763.30
30	Totals For COMMERCIAL FOUNDATIONS	$11,292.00	-$3,000.00	$0.00	$0.00	$8,292.00
30	Totals For CONTRACTED COMMERCIAL	$454.00	-$241.25	-$129.29	$0.00	$83.46
30	Totals For COORDINATED CARE	$216,179.00	-$93,977.41	-$111,775.06	$600.01	$11,026.54
30	Totals For DSHS	$70,023.00	-$21,523.32	-$38,280.68	$0.00	$10,219.00
30	Totals For FCHN	$195,657.00	-$139,056.11	-$42,719.61	$1,304.50	$15,185.78
30	Totals For IPN	$55.00	-$20.41	-$34.59	$0.00	$0.00
30	Totals For KAISER	$6,999.00	-$5,714.67	-$1,499.94	$215.61	$0.00
30	Totals For LCA	$26,400.00	-$22,140.00	-$4,060.00	$0.00	$200.00
30	Totals For MEDICAID COMMERCIAL	$398,386.00	-$132,939.20	-$244,692.80	$2,443.32	$23,197.32
30	Totals For MEDICARE	$3,723,687.00	-$1,819,239.29	-$1,853,265.91	$6,593.18	$57,774.98
30	Totals For MEDICARE ADVANTAGE	$2,030,582.00	-$958,091.08	-$985,512.10	$3,658.84	$90,637.66
30	Totals For MEDICARE RR	$7,692.00	-$4,045.10	-$3,646.90	$0.00	$0.00
30	Totals For MOLINA	$62,595.00	-$26,354.32	-$35,371.54	$24.30	$893.44
30	Totals For MULTIPLAN	$4,369.00	-$2,375.01	-$1,594.99	$0.00	$399.00
30	Totals For NO INSURANCE	$15,433.00	-$10,156.40	-$5,276.60	$0.00	$0.00
30	Totals For PREMERA	$736,026.00	-$454,644.24	-$267,642.69	$10,087.88	$23,826.95
30	Totals For REGENCE	$1,240,100.00	-$824,285.00	-$358,944.56	$4,376.25	$61,246.69
30	Totals For SELF PAY	$787,037.00	-$679,491.06	-$82,084.75	$2,123.00	$27,584.19
30	Totals For TRICARE	$1,287.00	-$801.85	-$485.15	$0.00	$0.00
30	Totals For UNITED HEALTHCARE	$235,518.00	-$161,307.83	-$60,194.42	$2,498.52	$16,514.27
Totals For Bellevue		$10,791,128.00	-$6,031,247.18	-$4,342,203.21	$42,962.36	$460,639.97
Bellingham
35	Totals For AETNA	$75,382.00	-$45,593.53	-$23,321.97	$0.00	$6,466.50
35	Totals For CIGNA	$36,640.00	-$26,159.20	-$9,112.80	$0.00	$1,368.00

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

 

[Fluff]

Could you please confirm what you are after, as what you have supplied, is as per your OP, not as per you subsequent requirements

 

[kenpcli]

Could you please confirm what you are after, as what you have supplied, is as per your OP, not as per you subsequent requirements

The first in the lookup table. The second is the raw data. the third is what I need it to look like afterwards.

I need it to look at the site name and then the legend and fill down the site number and change each time the site name changes.

 

[Fluff]

So have you ditched your requirements from post#5?

 

[kenpcli]

So have you ditched your requirements from post#5?

This is such a major project, I am just trying to get one piece solved at a time, if that makes sense.

 

[Fluff]

In that case how about

Sub FillDownLookup()

    Dim Ar As Areas
    Dim Rng As Range
    Dim ValU As Long
    
    With Sheets("Analysis")
        Set Ar = .Range("A6:A" & .Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
        For Each Rng In Ar
            Rng.Value = Evaluate("VLookup(" & Rng.Offset(-1).Resize(1).Address & ",'Legend Site'!A1:B1000, 2, False)")
        Next Rng
    End With
    
End Sub