Solved

Advanced Excel Programming - Matching columns to tick boxes.

Posted on 2013-12-05
13
191 Views
Last Modified: 2013-12-09
Hi guys,

I have two spreadsheets in Excel.  One is a client listing and one is a policy listing.

My client listing has all my clients listed going down one column.
My policy listing has multiple rows of policies belonging to one client.

I want to have Excel match the client name from the policy listing and the client listing for each of the rows in the policy listing and then look at another column on the policy listing for the type of policy and put an X in a box in the client's row on the client listing if that policy type exists.

I've put examples up on the web:

http://www.bbalpha.com/excel.htm

Thanks!

Sherry
0
Comment
Question by:shoshe933
  • 7
  • 6
13 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39698625
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
0
 

Author Comment

by:shoshe933
ID: 39698717
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39700367
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
0
 

Author Comment

by:shoshe933
ID: 39700983
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39701393
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
0
 

Author Comment

by:shoshe933
ID: 39701733
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
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:shoshe933
ID: 39701800
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!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39701817
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
0
 

Author Comment

by:shoshe933
ID: 39702126
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.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39702340
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
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39703058
Did you have any chance to try the proposed solution ?
gowflow
0
 

Author Closing Comment

by:shoshe933
ID: 39706942
This is fantastic!!! This will save me hours of work every time I need to update.

Thank you so much gowflow!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39707055
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
0

Featured Post

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

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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

17 Experts available now in Live!

Get 1:1 Help Now