Excel macros are killing me!

[brogi_bear]

Hi all... newbie here

I have been tasked to create a macro for HR regarding insurance bills in which i have to compare two different files- and they are not formatted the same (for example one has department codes, the other doesn't and has items in different rows and columns.) What both documents have in common though are names (first & last), the type of Premium (insurance plan type) and the total of that premium per person such as: Joe Dirt | $107.07 | Dental Plan. Although it would be ideal to replicate the dept code from one to another i can't figure how i can do that from different files. The end goal is to highlight the differences between both files and highlight in green if it matches, yellow if it doesn't match, and red if there is missing info. Does anyone know how i can achieve the end goal? or where i can start, as i cant find a formula that can meet these demands, i am starting to think it is impossible.

thanks!! I appreciate any insight and pointers!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of the sheets to be compared. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also what are the full names of the files including the extension and what are the sheet names involved?

 

[number kruncher]

I would turn to Power Query first to compare any lists like this, rather than VBA.

Then use Conditional Formatting on the resulting Combined Table to give you the colours you need.

 

[brogi_bear]

Hi! thanks for the reply... The spreadsheets are too big to use the XL2BB i will do the other method through box/dropbox as soon asap.. the firewall wont let me get there atm

 

[brogi_bear]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of the sheets to be compared. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also what are the full names of the files including the extension and what are the sheet names involved?

Hi so i will share a link.

Essentially, what needs to be done is to compare two worksheets and to highlight the differences such as: green=matches; yellow=no match; red=missing in either worksheet.
Since the documents are somewhat different, the first thing is that the "downloadbill" needs is to have a new column added and the departments to be added to each person just like the "list bill" worksheet has. Then, the matching (to highlight) needs to be done in the names columns, department, Benefit Plan type (to match "downloadbill" rows per premium), and the premium price itself. and finally go get a total of all premiums per department.

That is why i am so lost, i do not know how much capabilities excel has to do the tasks required to compare both of these sheets the way the person expects it. Especially since every month names are added and other are removed. but anything helps really..

thank you in advance

Here is my headache..

 

[mumps]

Benefit Plan type (to match "downloadbill" rows per premium), and the premium price itself. and finally go get a total of all premiums per department.

I can see the Benefit Plan type in the List Bill sheet but not in the downloadbill sheet. How is this comparison to be done if the data doesn't exist in the downloadbill sheet? There are many premiums in the downloadbill sheet and only Sum of Premium in the List Bill sheet so how is this comparison to be made? Which premiums are to be totaled and where do you want to put the totals? Please describe in detail, step by step, referring to specific cells, rows, columns and sheets using a few examples from your data. It would also be extremely helpful if you could post a sample of two or three rows of data of what the end result would look like using the data in the file you posted.

 

[brogi_bear]

I can see the Benefit Plan type in the List Bill sheet but not in the downloadbill sheet. How is this comparison to be done if the data doesn't exist in the downloadbill sheet? There are many premiums in the downloadbill sheet and only Sum of Premium in the List Bill sheet so how is this comparison to be made? Which premiums are to be totaled and where do you want to put the totals? Please describe in detail, step by step, referring to specific cells, rows, columns and sheets using a few examples from your data. It would also be extremely helpful if you could post a sample of two or three rows of data of what the end result would look like using the data in the file you posted.

Yes that's were everything gets out of hand in my humble opinion, and seems impossible.

The download bill has columns with each of the insurance plans (this is the same as benefit plan type on the List bill) and the total premium per person and plan so technically column O and beyond are irrelevant on the download bill, and it's missing the "department column."

So essentially, everything that matches needs to be highlighted- in whichever way possible. I am open to any advise how to do this.. perswonally as a noob i was thinking something like this:
I have made a macro that sorts out the users in to lastname/first name order with accending dept code (10 to 80)
1. make sure both sheets have a department code for each person (create column next to name)- add department code to download bill based on listbill code for each person
2. highlight the Benefit Plan type on the "list bill" compared the "download bill" - green matches, yellow no match, red not found
3. highlight the "sum of premium" on listbill and "XYZ premium" on download bill if each matches- we are checking to make sure the premium prices match on both sheets as we are comparing/checking the differences in prices for the peoples insurance plans. So if there is a price difference we would contact the insurance company and dispute any difference in charges. So, for example, if Dental has a different price for Jason Adams then it would highlight yellow on both documents, if it matches then highlight green and if it cant find anything highlight red for the cell - or whichever way is easier.. honestly i have no idea what would be easier as i am excel noob.. i can troubleshoot it but macros/vba is another planet.

the total would be nice to go near the name so we know to who it belongs, or to create a 3rd spreadsheet that just list all items that match per person on both sheets.

And if there is another way that you suggest i am all in, this whole comparison process takes 2-3hours so we just want to cut that to make it simpler to compare the prices of the "premiums" that each person has.

 

[brogi_bear]

I can see the Benefit Plan type in the List Bill sheet but not in the downloadbill sheet. How is this comparison to be done if the data doesn't exist in the downloadbill sheet? There are many premiums in the downloadbill sheet and only Sum of Premium in the List Bill sheet so how is this comparison to be made? Which premiums are to be totaled and where do you want to put the totals? Please describe in detail, step by step, referring to specific cells, rows, columns and sheets using a few examples from your data. It would also be extremely helpful if you could post a sample of two or three rows of data of what the end result would look like using the data in the file you posted.

I dont even think it will be possible to highlight the benefit plan type as it wouldn't make sense on the list bill since it doesn't appear multiple times on the download bill so i think that as long as the name, the the department code, the premium per insurance plan (dental, vision, ect) on download bill, the Sum of premium on list bill, and get the total of each plan premium per department would be ideal... man can excel do that?

 

[mumps]

Using Wyatt Anderson as an example, he has the following Plan Types in List Bill: Dental, Long Term Disability, Vision and Voluntary Employee Life. In order to do a comparison to the premiums in downloadbill, I have to find those plan types in row 1 of downloadbill. It would be easy enough to find Dental and Vision but Long Term Disability and Voluntary Employee Life don't exist in row 1 of downloadbill so there is no easy way to make the comparison. The plan types of column K in List Bill would have to match the headers in row 1 of downloadbill.

 

[brogi_bear]

Using Wyatt Anderson as an example, he has the following Plan Types in List Bill: Dental, Long Term Disability, Vision and Voluntary Employee Life. In order to do a comparison to the premiums in downloadbill, I have to find those plan types in row 1 of downloadbill. It would be easy enough to find Dental and Vision but Long Term Disability and Voluntary Employee Life don't exist in row 1 of downloadbill so there is no easy way to make the comparison. The plan types of column K in List Bill would have to match the headers in row 1 of downloadbill.

Thanks for making me aware of that, Long term disability is LTD (row K on downloadbill) and Voluntary Term Life (row I in downloadbill) is Voluntary Employee Life