Computationbs on Duplicate Entries

[ace]

Column A Last Name First Name Date of Birth Current Status Sum A Sum B Sum
C Sum D Sum E Sum F

XYZ Baggins Frodo 1/1/2010 F 432 4525 4232 421 755 866
AXYZ Baggins Frodo 1/1/2010 P 654 532 757 797 321 853
XYZ Sully Jake 2/2/2010 F 43 32 543 45 23 543
AXYZ Sully Jake 2/2/2010 P 43 423 54 32 643 65
XYZ Wayne Bruce 1/14/2010 F 10 20 30 40 50 60
XYZ Dawson Jack 3/3/2010 F 43 32 543 45 23 543
AXYZ Dawson Jack 3/3/2010 P 654 73 352 874 232 876
XYZ Connor John 3/3/2010 P 100 432 87 985 123 439


If an employee has duplicate records in coulmn A with XYZ and AXYZ, and if
AXYZ has current status, P, then this should add the respective totals in sum
A, B, C, D, E and F and display it next to the AXYZ column.

If an employee like Bruce wayne has current status F, then he should be
highlighted in red.

If an employee like John Connor has curent Status P, then he should be left
as he is.

Preferably, the output should be on a new worksheet.
Is this possible without VBA code?
If yes, what is the best way to do it? The sheet has around 280 entries
including duplicates.

Are there any Excel formuale that would work in this situation?
I don’t have any experience in VBA code.

Kindly advice,
Any help would be greatly appreciated,
I am seriously stuck on this.

Thanks,
Ace

 

[Daryl S]

Ace -

If your data is always sorted by employee then by column A descending, then
you can put a formula in to calculate the sum as follows (assuming data
starts on row 3):

=IF(AND((B3=B2),(C3=C2),(A3="A"&A2),(E3="P")),SUM(F3:K3),0)

I am not sure what you meant by putting this next to the AXYZ column, but
you could insert a new column after column A and input this formula,
incrementing each column. In my formula, I only check for P on the AXYZ
row, so if you need to check that it is P for both the XYZ and AXYZ rows,
then you need another item in the AND() construct of ,(E2="P") in order
to check both records (see Jack Dawson). This puts a zero for the cases
that don't match, but you could change that to "" if you like.

If you want this on another workbook, then copy/paste the current data
before making the changes.

As for highlighting the name, it is very easy to highlight the F cells in
red using conditional formatting. You can check that out in Help.