Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 2nd, 2004, 05:03 PM
Posts: n/a
Default Checking Duplicate values and delete rows

Hello, everyone
I have a spreadsheet, like below, I want to check for duplicate values in columnA and then sum the relevant values in colB, eg: row 3 to 5 is the same, so sum(b3:b5), then delete the irrelevant rows leaving the total in column B
Any help will be appreciated.
Existing columns;
     col A col B
1 A0202231 -4.73
2 A0202232 135.09
3 A0202238 89.54
4 A0202238 -90.72
5 A0202238 -0.18
6 A0202243 -0.36
7 A0202282 22.6
8 A0202311 -0.12

Expected result as follows:
     col A col B
1 A0202231 -4.73
2 A0202232 135.09
3 A0202238 -1.36
4 A0202243 -0.36
5 A0202282 22.6
6 A0202311 -0.12

I have been using the following code in an attempt to achieve the result:
Private Sub cmdCheck_Click()
Dim X
Set X = Worksheets("Sheet1").Range("F1")
Set currentCell = Worksheets("Sheet1").Range("E1")
Do While Not IsEmpty(currentCell)
    Set nextCell = currentCell.Offset(1, 0)
    If nextCell.Value = currentCell.Value Then
        X = X + 1
    End If
        Set currentCell = nextCell
End sub
This does delete the duplicate rows but does not add the values together.
Many thanks

Old February 3rd, 2004, 02:58 AM
Friend of Wrox
Join Date: Oct 2003
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts

Try this code

Private Sub cmdCheck_Click()

Dim dblSum As Double

Do Until IsEmpty(ActiveCell)
    dblSum = ActiveCell.Offset(0, 1)
    Do While ActiveCell = ActiveCell.Offset(1, 0)
        dblSum = dblSum + ActiveCell.Offset(1, 1)
    ActiveCell.Offset(0, 1) = dblSum
    ActiveCell.Offset(1, 0).Select
End Sub

:D vemaju
Old February 3rd, 2004, 06:57 AM
Posts: n/a

Hi, vemaju
Thank you very much!
It works brilliantly!



Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking for duplicate entry in Database sharon5469 ASP.NET 1.0 and 1.1 Basics 1 November 18th, 2007 06:08 PM
How can you delete duplicate rows without using te subhasischakraborty SQL Server 2000 15 October 19th, 2007 06:51 AM
How to delete unique rows but with duplicate colum winder SQL Server 2005 3 January 3rd, 2007 04:57 PM
Dealing with duplicate rows/column values SQLScott SQL Server 2000 2 September 29th, 2005 09:04 PM
Checking for duplicate record in jsp Regornil JSP Basics 0 July 30th, 2004 01:55 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.