Advanced Excel Programming - Matching columns to tick boxes.

Pls as there are risky sites out there, kindly upload any workbook that you have sample data in so we can help you further.
Rgds/gowflow

Thanks gowflow

So, I need a formula that will match Account in the Policy Listing to the Account in the Client Listing and then put a check mark in the appropriate Medical, Dental, Vision, etc column if that name exists in the Coverage column on the Policy Listing.
Company-Listing.xlsx
Policy-Listing.xlsx

ok some questions

1) Are Company-Listing and Policy-Listing 2 different workbooks like you posted or 2 different sheets in 1 same workbook ?
2) If latter are 2 workbooks then are they in same directory ?

Lets start with this
gowflow

Hi gowflow,

I do have them as separate workbooks right now, but I could just as easily have them on different sheets within the same workbook.  I exported both lists from our MSCRM system so I know the Account names are all exactly the same.

Thanks,

Sherry

well for sure better have them in a same workbook I will develop the solution based on them in the same workbook. Pls confirm it is fine.

One last thing, I can see maybe this is a sample data what is the data that you have as steady and the one you want to build ?

My understanding is that you have Policy listing as base and then we could build Company-Listing leaving Col C and Col D empty as this data not available in Policy

So to summarize you have 2 options:

1) I can build you a macro that would reside in 1 file that is called Policy listing and when you activate this macro it build in a sheet called Output exactly what you want except without Col C and D

2) We can have these 2 sheets residing in 1 same workbook and when you activate the macro it will look in sheet Policy and Company and update Company with the X where it should be. The down side of this is if you have previous data there that is not in Policy then you would have some X removed or ... ??? you tell me how would the day to day functioning of this happens.

Gowflow

I made a new spreadsheet that combines both lists and shows exactly how it would be setup.  For future, what I would do is refresh the spreadsheet by cutting and pasting the data into this spreadsheet exactly as it is in there now on the policy listing and to the first four columns on the company listing sheet.

So, it would match column D in Policy Listing sheet to column A in Company Listing sheet.
Then it would look at column F in the Policy Listing sheet and match it to the column headings E-AE  (I matched them exactly to all of the options for that column F field could be) and if it is true it'll put an X in that box on the company name's row.


It wouldn't be limited to the amount of rows that I currently have on the spreadsheet. Right now, my full list has roughly 2,000 records on the policy listing sheet and 700 on the company listing sheet.
Spreadsheet.xlsx

You know, have it output it to a new sheet that picks up Column A, B, and D from the Company Listing and has column headings for all of the plan options - so it'll end up looking similar to the way I have the Company Listing currently set up.

It is so awesome of you to do this!

ok perfect. Do you have any objection to following:

1) To sort Sheet Policy Listing by Account Col D ?? if yes and you would like an other col too pls advise
2) Can I assume that the key to find an item is Col D Account ??? Like I would search Account in policy and Company and when found I update ???

pls advise
gowflow

1. No problem to sort the policy listing by column D
2. If I'm understanding correctly, yes the key item is the Column D in policy listing.  If Column D in policy matches column A in client listing then the macro then looks at column F on policy listing and puts an 'x' in the box of the matching named column on the company listing sheet.
What I don't want is to end up with each company name listed multiple times on the final company listing, if there are 5 separate policies on the policy listing the company listing would list the Account name once and then have 5 X's in the appropriate Medical, Vision, Dental, etc. columns.

ok here it is. This is how it works:

1) Download the file and make sure your macros are enabled.

2) I have simulated several examples so I dragged your original data to create more lines to make sure it update correctly.

3) This version will do the following:
When it finds the Co looks for the type of policy if it find it it puts an X in the appropriate col
if it does not find it (then it assumes this type of policy is new then it will create it at the end of the existing types (I have simulated at the end rows some types that were not there. You can create more in Policy file and see how it will update in Company.

4) If a Company exist in Policy and does not exist in Company it will create it in Col A after the last existing row also here I have simulated some that does not exist at the last couple rows.

5) Press on the Update button and it will  sort sheet Policy by Account then it will update data.

6) You noticed I used special formatting of the types of policy so you can view them easier. If you don't like this we can change that. we can group them in colors of they belong to groups to make it easier to read ....

Let me know
gowflow
Spreadsheet.xlsm

This is fantastic!!! This will save me hours of work every time I need to update.

Thank you so much gowflow!!

Your welcome dear glad I could help. Pls do not spare me case you would need any help I will keep this question open. Pls post any comment on a new question you would like help with and I will be glad to assist.
gowflow