Hi everyone,
I’m working in a sheet that has 4 tabs with information about insurance companies. The sheet is used for updating a database that tells users which states a company works in as well as what type of insurance they sell.
There are two tabs for states – add and delete, as well as two for insurance codes – add and delete as well. If Company A is marked with an x in the add column for AZ, then it will delete Arizona in the database. Same thing for the insurance codes. This is what it looks like currently:
The issue is that the upload to the database needs each state to link to each code. Basically, we can’t tell it to just “add AUTO to Company A”. Instead, you need to tell it to add AUTO to Company A for WI, then add it for IL, and so on.
The end result should look like this:
and so on far all of the states and codes. I understand how to do this algorithmically but have no idea how to turn it into a macro or even which functions to use. Because its relatively simple to do in a stepwise fashion I assume it is possible, I am just not sure how. It would take days or weeks to do in the actual sheet which has tens of thousands of rows.
Here are the steps involved in doing it manually:
- Count the number of states being added for each insurance company. In this case, Company A has 6 states.
- Under the first row in the CODE sheet, add that number minus one. In this case you add 5 rows under the row with AUTO, giving you 6.
- Copy and paste the states into the “States” column in the CODE sheet.
- Copy the CODE value for all of the cells until you reach the next code value.
- Do the same for the Company Name value and the “x” under “add” or “delete”.
I believe that does it. I understand how this works conceptually but keep in mind I am a total novice when it comes to computers in general and have never coded at all much less worked in BASIC. But this definitely seems doable.
Any ideas? Thank you!