x
Solved

# Excel - unique values sum formula

Posted on 2014-12-09
Medium Priority
280 Views
I need to create a formula which does not divide duplicate values twice. In the attached spreadsheet, I need to divide Compl/Deal when the value in Job is unique only. The current formula does it twice when duplicate values exist.
What is the best way to do this? A formula would certainly help.

Thanks.
Job.xlsx
0
Question by:saved4use

LVL 52

Expert Comment

ID: 40489927
What do you want to see in cells D3 and D4?
0

LVL 4

Assisted Solution

Brad Rubin earned 500 total points
ID: 40489931
This should work for you...Put the formula in E2 and fill it down.

It will do the calculation for each unique value in Column B2. Duplicates will be marked accordingly.

=IF(B2<>B1,C2/A2,"Duplicate")
0

Author Comment

ID: 40489935
I only want to see 1, NOT 2 1s.
0

LVL 61

Assisted Solution

HainKurt earned 500 total points
ID: 40489950
D2: =IF(B2<>B1,C2/A2,"")
fill down...
0

LVL 52

Assisted Solution

Martin Liss earned 400 total points
ID: 40489957
Here's a macro
``````Sub UniquePerc()
Dim strPrevJob As String
Dim lngRow As Long
For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(lngRow, 2) <> strPrevJob Then
Cells(lngRow, 4) = Cells(lngRow, 3) / Cells(lngRow, 1)
strPrevJob = Cells(lngRow, 2)
End If
Next
End Sub
``````
0

LVL 27

Accepted Solution

Glenn Ray earned 600 total points
ID: 40490075
If your data is always sorted by JOB, then the formulas by Brad Rubin and HainKurt should work.  However, only the last occurrence of each set of duplicates will contain the COMPL/DEAL formula.   If your values are not sorted, these formulas will not work.

If you only want the first value to contain the percentage calculation AND if there is a possibility of the data not being sorted by Job, insert this formula in cell D2 and copy down:
=IF(COUNTIF(\$B\$2:\$B\$100,B2)=1,C2/A2,IF(COUNTIF(\$B\$2:B2,B2)=1,C2/A2,""))

It inserts a blank if the job number is a duplicate (i.e., has occurred earlier in the list).  That can be modified to show any other value (ex., "Dup", "Duplicate", or 0 - zero).

Regards,
-Glenn
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.