Solved

Excel - unique values sum formula

Posted on 2014-12-09
6
249 Views
Last Modified: 2014-12-15
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
Comment
Question by:saved4use
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40489927
What do you want to see in cells D3 and D4?
0
 
LVL 4

Assisted Solution

by:Brad Rubin
Brad Rubin earned 125 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

by:saved4use
ID: 40489935
I only want to see 1, NOT 2 1s.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 56

Assisted Solution

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

Assisted Solution

by:Martin Liss
Martin Liss earned 100 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

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 150 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question