Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Computationbs on Duplicate Entries
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 dont have any experience in VBA code. Kindly advice, Any help would be greatly appreciated, I am seriously stuck on this. Thanks, Ace |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Computationbs on Duplicate Entries
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. -- Daryl S "ace" wrote: 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 dont have any experience in VBA code. Kindly advice, Any help would be greatly appreciated, I am seriously stuck on this. Thanks, Ace |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Duplicate entries | Excel Worksheet Functions | |||
Duplicate entries | New Users to Excel | |||
Duplicate entries | Excel Programming |