Reorder Rows and Sum column totals given specified criteria

[bwmuhich]

Hello fellow excel forum members!

I have a census of data in one tab which includes employees, their spouses and children's information, include last name, first name, what they are (i.e. Employee (E), Spouse (SP) or Child (C), and the monthly premium their policy costs.

In another tab, I need a formula that will take the data from the MASTER tab (see attached), and shift the information upwards so that only Employees appear in the attached FORMULA Sheet tab, not spouses or children, but I need this FORMULA Sheet tab to correctly capture the monthly premium from the MASTER tab for the correct employee.

So as the 3rd tab in the attached sample file shows, Heidi Adams has an insurance policy for herself and two children, so the premium that needs to be captured in the FORMULA SHEET tab in the column "Premium for C (if applicable)", needs to return a result of $202.00, so on and so forth.

Thank you so much in advance!

Be Well

[aydeegee]

Hi bwmuhich

I have nearly achieved what you want - but I cannot get the list of employees from the master sheet to the formula sheet. At the back of my mind I think I remember that there is a way of doing this, so now it will irritate me until I get very cross.

In the meantime, here my attempt at a solution. I had concerns that you might get 2 employees with the same surname - so I have dealt with that aspect. (Note that you must not employees with the same name adjacent to each other).

The solution is relatively straight forward using a macro - so I wonder what your objection is to using it?

Regards
Alastair

[bwmuhich]

Thank you for your suggestion, but I have found an alternate way to achieve my goals without using macros.

Be Well,
Brian

[aydeegee]

Hi Brian

That's good! Can you share with us the method of getting the employees on to the formula sheet - it's always good to know these things.

Regards
Alastair

[bwmuhich]

I had received wisdom previously from another excel forum member on a recent post entitled:

Formula needed for sequential ordering given a single search criteria

In this post, a forum member provided an array formula that took the data in my range from one tab and transferred it all over to another tab such as the formula tab.

So building off of that wisdom, I'm just using that same array formula and am going to reference a few extra columns I've created in my main tab off to the right that include the month premiums for each member type (E, SP, C).

Be Well,

[aydeegee]

Ok - glad you found an answer.

Regards
Alastair