andreaEdwards
asked on
How to construct Excel If-Then-Else VBA
I'm looking at 2 columns - "CompanyName" and "PlanID". While "PlanID" is a number, both are text variables.
I have over 5000 records and need to populate the "PlanID" column depending on the "CompanyName." I have a list of at least 60 "CompanyName(s)."
How can I achieve this with Excel VBA.
Thank you.
I have over 5000 records and need to populate the "PlanID" column depending on the "CompanyName." I have a list of at least 60 "CompanyName(s)."
How can I achieve this with Excel VBA.
Thank you.
One needs to have all the details before started to thing about coding. It's not only a question of If-Then-Else. You need to access the original data, search and loop through it, know how to organize the data and how to integrate it with already existing data if there is already something in the destination sheet.
Are you starting with an empty sheet or are the company names already on the sheet?
And in any case, when you say that you have 5000 records, where do they come from? A text file, a database, another worksheet, another workbook, some other source?
If it is a text file or a worksheet, how is the data organized in it. If it is a database, what type of database and how is the data is organized in it : simple table (what are the fields) or query/view/stored procedure?
Do you want something like the following:
<CompanyName1> <ID1>
<ID2>
<ID3>
<CompanyName2> <ID1>
<ID2>
<ID3>
or something else.
Are you starting with an empty sheet or are the company names already on the sheet?
And in any case, when you say that you have 5000 records, where do they come from? A text file, a database, another worksheet, another workbook, some other source?
If it is a text file or a worksheet, how is the data organized in it. If it is a database, what type of database and how is the data is organized in it : simple table (what are the fields) or query/view/stored procedure?
Do you want something like the following:
<CompanyName1> <ID1>
<ID2>
<ID3>
<CompanyName2> <ID1>
<ID2>
<ID3>
or something else.
ASKER
OK.
These are some of the given data definitions as far as CompanyName and PlanID's are concerned which is one excel file:
These are some of the given data definitions as far as CompanyName and PlanID's are concerned which is one excel file:
CompanyName PlanID
Aetna Better Health 03458546
Affinity Health Plan 2010186
AgeWell New York 03481927
Alphacare 03560441
Amida Care S99B001
Archcare Community Life 03466800
ArchCare Senior Life 03114514
Catholic Health – LIFE 03072740
CDPHP 1090384
CenterLight PACE 01234037
CenterLight Select 02710185
Centers Plan for Healthy Living 03506989
Complete Senior Care 03320725
Eddy Senior Care 01674982
Elant Choice 01825947
Elderplan 03173113
Elderplan dba Homefirst 03253707
Elderserve 03234044
Excellus BlueCross BlueShield 1390598
Extended MLTC 03549135
FFS (Fee-for-Service) 8888888
Fidelis Care at Home 01788325
Fidelis Care New York 2060193
Fidelis Medicaid Advantage Plus 02927631
GuildNet 01827572
Guildnet Medicaid Advantage Plus 02942923
Health Insurance Plan 03239801
Health Plus (Amerigroup) 2180196
Here is the otherExcel file where I have to populate the PlanID manually once I include the CompanyName. I'd like to populate the PlanID automatically depending on the CompanyName. I would think this would be done using the VBA IF:status CompanyName PlanID
Pending Healthfirst PHSP, Inc. 2090194
Pending Healthfirst PHSP, Inc. 2090194
Pending Healthfirst PHSP, Inc. 2090194
Pending Healthfirst PHSP, Inc. 2090194
Inactive HealthPlus (Amerigroup) 2180196
Inactive Catholic Health – LIFE 03072740
Pending Healthfirst PHSP, Inc. 2090194
Pending Catholic Health – LIFE 03072740
Pending Healthfirst PHSP, Inc. 2090194
Inactive Healthfirst PHSP, Inc. 2090194
Pending Healthfirst PHSP, Inc. 2090194
Pending Catholic Health – LIFE 03072740
Pending Healthfirst PHSP, Inc. 2090194
I hope this makes my question clearer...
Here you go with some of your data in the excel file.
a VLookup would be a good option:
Ex:
=VLOOKUP(B2,'PanID per company'!A:B,2,0)
vlookup.xlsx
a VLookup would be a good option:
Ex:
=VLOOKUP(B2,'PanID per company'!A:B,2,0)
vlookup.xlsx
ASKER
Each CompanyName only has 1 PlanID.
My initial issue (I think) is that the source of the Excel data download does not contain the PlanID, so I need to make the PlanID data dependent on the CompanyName data with an IF statement.
My initial issue (I think) is that the source of the Excel data download does not contain the PlanID, so I need to make the PlanID data dependent on the CompanyName data with an IF statement.
so you really need 2 sheets.
One sheet where you will have each Plan ID and the Company name that goes with it, and another sheet where you have the company name only. You would then use the VLookup, just like my last attachment. This would refer to the sheet with the list of company name with his Plan ID, and add the Plan ID besides the Company name in the other sheet.
Can you add an excel template? i will be able to use your template and make the VLookup for you
One sheet where you will have each Plan ID and the Company name that goes with it, and another sheet where you have the company name only. You would then use the VLookup, just like my last attachment. This would refer to the sheet with the list of company name with his Plan ID, and add the Plan ID besides the Company name in the other sheet.
Can you add an excel template? i will be able to use your template and make the VLookup for you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can't send additional data because of PHI (Personal Health Information). regulations.
I'll try your vLookup solution and get back to you.
Thank you.
I'll try your vLookup solution and get back to you.
Thank you.
I understand.
Or, you can also mimic how the data and sheet would look like.
The formula would then apply as soon you would use your official file.
You don't need to put a lot of rows of dummy data's. Just a couple.
Or, you can also mimic how the data and sheet would look like.
The formula would then apply as soon you would use your official file.
You don't need to put a lot of rows of dummy data's. Just a couple.
ASKER
Thank you Wilder 1626! To you goes all the glory! My problem is solved!
ASKER
My apologies for not getting back to you sooner. But thank you ever so much. Problem solved.
I'm glad i was able to help.
I'm not sure i fully understand what you are trying to do.
Can you please post a sample of your excel file, and also tell us what should also be the final result you would like to have.
But without understanding what you require, here is an example in attachment
vlookup.xlsx