Solved

Sum columns based on duplicate data macro

Posted on 2016-08-04
6
70 Views
Last Modified: 2016-08-07
if data in columns where A=B=C then sum Col D E and F

if i have data as below

Col A      Col B      Col C      Col D      Col E      Col F
123              456               789             0.1               2                 5
123              456               789             0.1               2                 5

Then it should Sum Col D E and F as data in each col of ABC are same.

attached is the file with input and output what i am looking for

found a macro but it has subtotal i do not need subtotal format
http://www.excelforum.com/l/691217-asdf.html
sample.xlsx
0
Comment
Question by:Nirvana
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41742174
I have just managed to achieve similar result with a Pivot Table.

See attached.
sample.xlsx
1
 

Author Comment

by:Nirvana
ID: 41742189
thank you Rob . Ideally i should work however the stakeholder is looking for a specific format. highlighting the repeated or duplicate rows and total to be highlighted in different color

what we can improvise in your solution is that remove sub total for col A and highlight rows with same value
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41742201
After I posted, I spotted something in your file as well. Is there a reason why the rows with 960 (?) in Col A weren't totalled as they were duplicated.

Can I ask why you don't want to use the SUBTOTAL function? It does what you are trying to do, other than it will add a subtotal row even if there is only a single entry.

Will there be occasions where the entry in col A is duplicated with different values in col B or col C?

Thinking that SUBTOTAL could still be used with some VBA to automate along these lines:

Add column Concatenating Col A, B & C to get unique identifiers
Sort Data on Identifier
Apply Subtotal with a count function on Identifier
Subtotal will insert rows and will add header of "Count of Identifier"
Apply Filter and Filter on "Count of" column for "Count of *" and result column where result = 1
Delete the visible rows to get rid of the rows with single entry subtotal
Unfilter
Do Find and Replace looking for "SUBTOTAL(3," and Replace with "SUBTOTAL(9,"  - for the arguments in the subtotal function 3 does a count and 9 does a sum.
Do Find and Replace for "Count of *" and Replace with "Sum of" or "" to remove.
Apply 2 Conditional Formatting rules:
   1) where count of col A is more than 1 highlight pale green
   2) where col A is blank highlight dark green

Does that sound feasible?

Thanks
Rob H
1
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Nirvana
ID: 41742240
Hi Rob,

I have internally done exactly what you have done. concatenated and created a subtotal. however, to provide it specific leadership team they wanted in specific format o had to put everything in that format copy pasting values and removing subtotals for single values etc., at times people are so concerned about format rather the results.

one other reason could be stakeholders receive the file from different regions if we change the format it will be additional work for them to consolidate

i found some help in the below sites, however unable to puttogether
http://stackoverflow.com/questions/21660648/excel-check-for-duplicate-rows-based-on-3-columns-and-keep-one-row

thank you again for understanding
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41743787
Please try this to see if this is what you were trying to achieve.
In the attached, click the button on Output Sheet to get the desired output.
If you have issue downloading and opening the attached workbook due to a temporary bug in the forum, first download and save it on your system and then open it.

Sub ReArrangeData()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, i As Long
Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Output")
dws.Cells.Clear
sws.Range("A1").CurrentRegion.Copy dws.Range("A1")
key2:=dws.Range("B1"), order2:=xlAscending, key3:=dws.Range("C1"), order3:=xlAscending, Header:=xlGuess
lr = dws.Cells(Rows.Count, 1).End(xlUp).Row
Range("G2:G" & lr).Formula = "=A2&B2&C2"
dws.Range("A1").CurrentRegion.Sort key1:=dws.Range("G1"), order1:=xlAscending, Header:=xlGuess
dws.Range("H2:H" & lr).Formula = "=IF(OR(G2=G3,G2=G1),0,1)"
dws.Range("H1").Value = "Formula"
dws.Range("H2:H" & lr).Value = Range("H2:H" & lr).Value
dws.Range("A1").CurrentRegion.Sort key1:=dws.Range("H1"), order1:=xlAscending, Header:=xlYes
For i = lr To 3 Step -1
    If dws.Cells(i, 7) = dws.Cells(i - 1, 7) And dws.Cells(i, 8) <> 1 Then
        dws.Rows(i + 1).Insert
    End If
Next i
lr = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each rng In dws.Range("D1:D" & lr).SpecialCells(xlCellTypeConstants).Areas
    DoEvents
    If rng.Cells(rng.Rows.Count).Offset(0, 4) = 0 Then
        rng.Cells(rng.Rows.Count).Offset(1, -1) = "Total"
        rng.Cells(rng.Rows.Count).Offset(1, 0) = Application.Sum(rng)
        rng.Cells(rng.Rows.Count).Offset(1, 1) = Application.Sum(rng.Offset(0, 1))
        rng.Cells(rng.Rows.Count).Offset(1, 2) = Application.Sum(rng.Offset(0, 2))
        rng.Cells(rng.Rows.Count).Offset(1, -1).Resize(1, 4).Font.Bold = True
        rng.Offset(0, -3).Resize(rng.Rows.Count, 6).Interior.Color = RGB(146, 208, 80)
        rng.Cells(rng.Rows.Count).Offset(1, -3).Resize(1, 6).Interior.Color = RGB(0, 176, 80)
        rng.Cells(rng.Rows.Count).Offset(1, -3).Resize(1, 2).Borders.LineStyle = xlNone
    End If
Next rng
dws.Columns("G:H").ClearContents
dws.Rows(1).Interior.ColorIndex = xlNone
Application.ScreenUpdating = True
End Sub

Open in new window

sample.xlsm
0
 

Author Closing Comment

by:Nirvana
ID: 41746388
just genius
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now