I have a problem that I am not sure where to even start at, but I am trying to create a VBA solution to my issue. I have two tables I need to compare values against and based on that comparison I have to generate a comma delimited list value.
Here is a sample of the data I am reading in and what I am hoping to get out:
<tbody>
</tbody>
Reference Table
<tbody>
</tbody>
Here is how it works:
I am stumped as the best way to approach this problem. Have been able to create VBA code to un-delimit the alias column and create individual rows for each alias value (I am not sure if that is the best approach). Even though I was able to get to that point I haven't been able to get much past a complex Vlookup and just find the first time the value is presented. I haven't been able to figure out how to manage multiple potential values and only displaying a single unique value.
Here is a sample of the data I am reading in and what I am hoping to get out:
Specialty Values | Expected Output |
Pulmonology | Lung Care, Pulmonology |
Ambulatory Procedure Center, General Surgery | Surgery |
Obstetrics/gynecology | Obstetrics & Gynecology, Women's Health |
General Surgery, Vascular Surgery | Surgery, Vascular Surgery |
<tbody>
</tbody>
Reference Table
Specialty | Alias | Inc. W | Inc. X | Inc. Y | Inc. Z | Rollup | |
<tbody> </tbody> | |Ambulatory Surgery Center|Ambulatory Procedure Center|Surgical Services| | 1 | 1 | 1 | 1 | |Surgery| | |
General Surgery | |Surgery: General|Surgery, General|Surgery|Surgery - General|Surgery, General|Surgery: General|Spine/Trauma/General|Trauma/General|General Surgery (Non-ABMS)||Surgery- General,Thoracic,Vascular|Hernia Surgery|Gen Surgery|Hepatobiliary Surgery| | 1 | 1 | 1 | 1 | |Surgery| | |
Ob-Gynecology | |Obestetrics & Gynecology|Obstetrics and Gynecology|Obstetrics & Gynecology|Obstetrics-Gynecology|OB-Gyn|OBGYN|OB/Gyn|OB/GYN|Obstetrics/Gynecology|ABOG|Pediatric and Adolescent Gynecology|Obesetrics & Gynecology| | 1 | 1 | 1 | 1 | |Obstetrics & Gynecology|Women's Health| | |
Pediatric Surgery | |Pediatric Plastic Surgery|Pediatric Orthopedic Surgery|Pediatric General Surgery|Pediatric Transplant Surgery|Pediatric Transplantation|Pediatric Thoracic Surgery|Pediatric Craniofacial Medicine|Pediatric Cardiothoracic Surgery|Pediatric Orthopaedic Surgery|Pediatric Surgery (Non-ABMS)|Pediatric Transplant Hepatology|Pediatric General and Thoracic Surgery| | 1 | 1 | 1 | 1 | |Pediatric Surgery| | |
Pulmonary Diseases | |Pulmonology|Pulmonary Disease|Pulmonary|Pulmonary & Critical Care Medicine|Pulmonologist|IM Consultation-Heart & Pulmonary Disease|Pulmonary Medicine|Pulmonary & Critical Care|Pulmonary and Sleep Medicine|pulmonary)| | 1 | 1 | 1 | 1 | |Lung Care|Pulmonology| | |
Surgery, General Vascular | |Surgery: Vascular|Microvascular Surgery|Surgery- General,Thoracic,Vascular|General Vascular Surgery| | 1 | 1 | 1 | 1 | |Vascular Surgery| | |
Vascular Surgery | |Specialist/Technologist Cardiovascular - Vascular Specialist|Vascular/Endovascular|Vascular| | 1 | 1 | 1 | 1 | |Vascular Surgery| |
<tbody>
</tbody>
Here is how it works:
- Look first at Specialty Values from primary table
- Next see if that Specialty Value exists in the first column of the reference table "Specialty"
- If it is found add the values in the "Rollup" column to a list
- Next see if that Specialty Value exists in the second column of the reference table "Alias"
- This is used to handle misspellings or common references to a specialty
- Value could potentially exist under multiple Specialty/Alias combinations
- If it is found add the values in the "Rollup" column to a list
- Remove any duplicates of values, for example if someone has a specialty of General Surgery and Ambulatory Surgery Center, they would have a rollup of Surgery twice, but it should only be shown once in the comma delimited list
- NOTE: In this example columns 3-6 all have "1"'s in the values. There are some scenarios where they might have a zero, and if so I would need to ignore that row in my lookup.
I am stumped as the best way to approach this problem. Have been able to create VBA code to un-delimit the alias column and create individual rows for each alias value (I am not sure if that is the best approach). Even though I was able to get to that point I haven't been able to get much past a complex Vlookup and just find the first time the value is presented. I haven't been able to figure out how to manage multiple potential values and only displaying a single unique value.