Copy sheet and add Location number

K

kenpcli

Board Regular
Joined
Oct 24, 2017
Messages
129
I am trying get a macro to look down column A and each change in site name add their location number underneath it.

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Fluff said:
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?
Click to expand...

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.
 
Upvote 0
Could you supply a sample of the look table, the raw data, & what result you are getting?
 
Upvote 0
Fluff said:
Could you supply a sample of the look table, the raw data, & what result you are getting?
Click to expand...
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]
 
Upvote 0
[TABLE="width: 255"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Albuquerque[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Bellevue[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Bellingham[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Boise[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Chehalis[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Great Falls[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Kennewick[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Lewiston[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Olympia[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Portland[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Silverdale[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]Spokane[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]Tacoma[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Tualatin[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Vancouver[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Yakima[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

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





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

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.
 
Upvote 0
So have you ditched your requirements from post#5?
 
Upvote 0
In that case how about
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Fill and Sort
2
Replies
12
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top