Solved

How to construct  Excel If-Then-Else VBA

Posted on 2015-01-25
13
62 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:andreaEdwards
  • 6
  • 5
13 Comments
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Hi

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
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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.
0
 

Author Comment

by:andreaEdwards
Comment Utility
OK.

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

Open in new window

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

Open in new window

I hope this makes my question clearer...
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
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
0
 

Author Comment

by:andreaEdwards
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
Comment Utility
another way would be to combine both, an IF statement and a VLookup
if vlookup
=IF(ISNA(VLOOKUP(B5,'PanID per company'!A:B,2,FALSE)),"", IF(VLOOKUP(B5,'PanID per company'!A:B,2,FALSE)="","", VLOOKUP(B5,'PanID per company'!A:B,2,FALSE)))

 

see attachment
vlookup.xlsx
0
 

Author Comment

by:andreaEdwards
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
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.
0
 

Author Comment

by:andreaEdwards
Comment Utility
Thank you Wilder 1626!  To you goes all the glory!  My problem is solved!
0
 

Author Closing Comment

by:andreaEdwards
Comment Utility
My apologies for not getting back to you sooner.  But thank you ever so much.  Problem solved.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
I'm glad i was able to help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now