Excel - unique values sum formula

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
saved4useAsked:
Who is Participating?
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
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
 
Martin LissOlder than dirtCommented:
What do you want to see in cells D3 and D4?
0
 
Brad RubinConnect With a Mentor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
saved4useAuthor Commented:
I only want to see 1, NOT 2 1s.
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
D2: =IF(B2<>B1,C2/A2,"")
fill down...
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
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

Open in new window

0
All Courses

From novice to tech pro — start learning today.