I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.

COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner

I want to end up with rows that each contain unique info in COLUMN B.

Hi,
I have over 2000 records. Need code to
check duplicate row ("A") then
sum ("E")
and delete row.

example
A B C D E
1|4 6
2|5 9
3|4 3
4|6 5
5|4 2

After code should look thus

A B C D E
1|4 11
2|5 9
3|6 5

Hi. I have Windows XP, Microsoft Office 2003 (Excel 2003)


I have over 20,000 items that I must sort through. I am dealing with book titles and need to sort them by how many times each book has been checked out. But there are often several copies of books so I need to do the following in excel:
identify the duplicates sum up the total for the duplicate copies of the same book delete the copies and leave only one line with the book name/author/and now TOTAL number of checkouts Here is a sample of what my data looks like:

Column A________________________Column B_______Column C
Title Author Checked Out
1 gaping wide-mouthed hopping frog______Tryon, Leslie_________114
4 pups and a worm____________________Seltzer, Eric__________135
A bade case of stripes_________________Shannon, David_______102
A bargain for Frances__________________Hoban, Russell________131
A bargain for Frances__________________Hoban, Russell________107
A bargain for Frances__________________Hoban, Rusell_________102
A bear for all seasons__________________Fuchs, Diane_________103
A bear for all seasons__________________Fuchs, Diane_________102


There are two duplicates for "A bargain for Frances" and one duplicate for "A bear for all seasons. Here is what I would like for it to look like:

Column A___________________Column B_____________Column C
A bargain for Frances____________Hoban Russell______________340
A bear for all seasons____________Fuchs, Diane______________205

Is there a way to do this? I know how to do it manually through the Conditional Sum Wizard but I have so many items that it would take literally forever. Is there a way to have excel to do in one shot? Some kind of formula?

If you guys can, please provide instructions step by step...in the past I have used excel for only very general things. So writing formulas, even COUNTIF or SUMIF was totally new to me as of only a few weeks ago.

thank you so much for your help!
Irina

Hi. I have Windows XP, Microsoft Office 2003 (Excel 2003)



I have over 20,000 items that I must sort through. I am dealing with book titles and need to sort them by how many times each book has been checked out. But there are often several copies of books so I need to do the following in excel: identify the duplicates sum up the total for the duplicate copies of the same book delete the copies and leave only one line with the book name/author/and now TOTAL number of checkouts Here is a sample of what my data looks like:

Column A______________________ Column B_________ Column C
Title Author Checked Out
1 gaping wide-mouthed hopping frog ___Tryon, Leslie ________114
4 pups and a worm __________________Seltzer, Eric ________135
A bade case of stripes _______________Shannon, David _____102
A bargain for Frances ________________Hoban, Russell ______131
A bargain for Frances ________________Hoban, Russell ______107
A bargain for Frances ________________Hoban, Rusell _______102
A bear for all seasons ________________Fuchs, Diane _______103
A bear for all seasons ________________Fuchs, Diane_______ 102


There are two duplicates for "A bargain for Frances" and one duplicate for "A bear for all seasons. Here is what I would like for it to look like:

Column A ____________Column B ___________Column C
A bargain for Frances ____Hoban Russell __________340
A bear for all seasons ____Fuchs, Diane___________ 205

Is there a way to do this? I know how to do it manually through the Conditional Sum Wizard but I have so many items that it would take literally forever. Is there a way to have excel to do in one shot? Some kind of formula?

If you guys can, please provide instructions step by step...in the past I have used excel for only very general things. So writing formulas, even COUNTIF or SUMIF was totally new to me as of only a few weeks ago.

thank you so much for your help!
Irina

Hi All,

I have a data dump of 16499 rows, in column B there are lists of "usernames" and column H "Gross Revenue". I would like column J to display the sum of the duplicate values from column H - or just the value of column H if there have not been any duplicates and then delete any duplicate rows.

I am not sure where to begin - VBA of forumlae?

Peace.

Hi There,

I am very confused about my macro I wrote which has data similar like this:

Number/ Name/ Amount1/ Amount2/ Amount3

100 XAOO 10 5 1
101 XAOB 20 10 2
100 XAOO 30 15 3
102 XAOC 40 20 4
102 XAOC 50 30 5

and it should be like this at the end:

Number Name Amount1 Amount2 Amount3

100 XAOO 10 20 4
101 XAOB 20 10 2
102 XAOC 90 50 9


So I thought I was done with this macro because it is working until I realised it doesnt add the last 3 rows to the consolidated list.

My original data comes from differentsheets and being organised in a seperate temporary sheet which is called "Notinuse3". So this macro runs with this tempo sheet. I run the macro with different data and the last try showed me while it is clearing content (which are the duplicated rows) it always does not iclude the last couple of rows. Here is my code, please check this out.

VB:

 
Dim rInput, lastrange As Range 
Dim oDic As Object 
Dim nTotal(), vInput() 
Dim i2 As Long, j, k, lastrownumber As Long 
 
With Application 
    .ScreenUpdating = False 
    Sheets("NotINuse3").Activate 
    Set rInput = Range("A1", Range("E" & Rows.Count).End(xlUp)) 
    vInput = rInput.Value 
    Redim nTotal(1 To UBound(vInput, 1), 1 To 5) 
    Set oDic = CreateObject("Scripting.Dictionary") 
    With oDic 
        For i2 = 1 To UBound(vInput, 1) 
            If Not .exists(vInput(i2, 1)) Then 
                j = j + 1 
                For k = 1 To 5 
                    nTotal(j, k) = vInput(i2, k) 
                Next k 
                .Add vInput(i2, 1), j 
            ElseIf .exists(vInput(i2, 1)) Then 
                For k = 3 To 5 'Starting from K=3 because we don't want to add up first 2 rows
                    nTotal(.Item(vInput(i2, 1)), k) = nTotal(.Item(vInput(i2, 1)), k) + vInput(i2, k) 
                Next k 
            End If 
        Next i2 
    End With 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    Set lastrange = Range("A2:E" & lastrownumber) 
    lastrange.Clear 
     ''''''''''''Columns("A:E").Clear   (this one also does the same)
    Range("A2").Resize(j, 5).Value = nTotal 
    Application.ScreenUpdating = True 
End With 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines





Thank you so much for all the help !

Hello All,

Is there a way in Excel to delete any rows with NO red cells (except header row) in them unless they have a duplicate value in column A, then keep those rows as well?

Here's a before and after...


BEFORE MACRO AFTER MACRO Customer Number Customer Name Country Salesman Color VIP Customer Number Customer Name Country Salesman Color VIP ABC123 John Smith U.S.A. Zippy Blue Yes ABC123 John Smith U.S.A. Zippy Blue Yes ABC123 John Smith U.S.A. Zippy Red No ABC123 John Smith U.S.A. Zippy Red No ABC124 George Washington U.S.A. Woody Green Yes ABC128 Dave Green U.S.A. Mary Orange No ABC125 Abraham Lincoln U.S.A. Woody Yellow No ABC126 May Williams U.S.A. Harry White No ABC127 Kay Potter U.S.A. Sheila Black No ABC128 Dave Green U.S.A. Mary Orange No ABC129 Steve Oshen U.S.A. Johnny Peach Yes


Thanks

Dear all,

I have a hard time with finding any duplicate value. and add the corresponding value1 and value2 with "+" sign. ( not sum the total value). finally remove the duplicate row.
So suppose initially, I have Five column.
column A, column B, column C, column D, Column E
2000 78100 AAA 200 APPLE
2001 78122 BBB 33 PEAR
2002 78101 BBB 33 PEAR
2004 78103 FFF 100 ORANGE
2005 79100 BBB 33 PEAR
2006 79101 FFF 44 ORANGE

after running the code,

it would look like,

column A, column B, col C, col D, Col E
2000 78100 AAA 200 APPLE
2001+2002+2005 78122+78101+79100 BBB 33+33+33 PEAR
2004 +2006 78103 +79101 FFF 100+44 ORANGE




I will appreciate any clue or help.
Thank you very much.

My sheet has 5000 rows I have highlighted each row with a D that is a duplicate.

I have created this code to delete all lines with a D in O:O

Its just very slow

Is there a better way?

Code:

Sub cleardups()

For n = Range("A6", Range("A6").End(xlDown)).Cells.Count To 1 Step -1
    If Range("A6").Offset(n, 14).Value = "D" Then
        Range("A6").Offset(n, 14).EntireRow.Delete
    End If
Next n

End Sub

How do you delete duplicate rows?

Example:

Part
1234
1234 (delete)
1345
1345 (delete)
2345
2345 (delete)