Duplicate in column

Question

I'm trying to write a code to solve this little issue that I have, but can't seem to get it. I have multiple columns in an excel spreadsheet and in one of those columns, there are duplicate values. What I want to do is to remove the second/duplicate value but also take the integer value in one of the other columns and add it to the row where the first value is and after that delete that "second" row. I tried with the .RemoveDuplicates command, but it just deleted the duplicate value and shifted the whole column up, so I can't add the values as I wanted.

Here's an example

Sample data

I only need the duplicates removed from one of the columns, D, here we see that row 5 and 10 are similar in that column and what I want to do, is to add the numbers from column C in row 5 and delete row t´10, so I'll end up with this

After image

I really hope any of you can help as I'm a bit lost. Thanks!

https://www.excelforum.com/excel-formulas-and-functions/937945-remove-duplicates-without-removing-cells-leave-blank.html ? — Marcucciboy2, Oct 25 '18 at 00:44
Your question is not clear. Edit your question and provide some sample data with "before" and "after" and then explain the logic. Post a comment when you've done that. — teylyn, Oct 25 '18 at 00:50
@Marcucciboy2 Thanks for the comment, but this makes it difficult to add the values from the other columns since I won't know which row it's a duplicate of — Niktaneous, Oct 25 '18 at 00:52
You say you're 'trying to write a code' yet I see no code in your question. — , Oct 25 '18 at 01:20
@Marcucciboy2 there is a difference if you look at row 5 and 10, the values in column C has been added and row 10 have been removed — Niktaneous, Oct 25 '18 at 01:30
Ping me if you [edit] your question to show your coding effort. This isn't a free code writing site and showing some level of original effort is expected. — , Oct 25 '18 at 01:31
SO is not a code writing service, where you post your requirements and we write code to meet them. We're more than happy to help once you've made an effort to do something and run into an issue. When that happens, you can come back and explain the problem you've encountered, include the relevant portions of your code, and ask a specific question about that code and we'll try to help. — Ken White, Oct 25 '18 at 01:33
@Jeeped sorry for bothering, I just don't know what I could do and was hoping for a hint or anything to help me going — Niktaneous, Oct 25 '18 at 01:36
@KenWhite didn't expect anyone to write my code for me. I've spent some hours on this and nothing works for me. I'm new to VBA and was hoping someone would give a little hint — Niktaneous, Oct 25 '18 at 01:38
I think I'm not understanding how this is different from remove duplicates? Your example shows deleting that row which is a duplicate as far as i can tell — Marcucciboy2, Oct 25 '18 at 02:14
@Niktaneous, for your encouragement - you may want to have a look at this link, do a bit of more trials and see if you can get to a point to ask this question again with where you are stuck. SO site tends to respond better if you can provide 1. Where you are (your first screenshot), 2. Where do you want to end up (your second screenshot), and 3. Most Importantly, what have you tried so far. What you are missing is a piece of code that you are trying to fix. Once you have that code, post it here and the super-intelligent folks over here will help you solve your problem in minutes if not seconds — Bharat Anand, Oct 25 '18 at 02:50
https://stackoverflow.com/questions/24377197/iterating-through-populated-rows-in-excel-using-vba — Bharat Anand, Oct 25 '18 at 02:51
Well, it's been 13 hours and you still haven't added anything substantive to *'I'm trying to write a code..'* so good luck with your project. — , Oct 25 '18 at 14:01

score 0 · Answer 1 · answered Oct 25 '18 at 01:31

0

Without code, you could use the advanced copy to copy unique values into another range, sumif to get your total and index/match to bring in the other columns. Once you get that figured out, record it as a macro and clean it up.

answered Oct 25 '18 at 01:31

wallyeye

116
5

score 0 · Answer 2 · answered Oct 25 '18 at 06:47

Resume your data with Pivot Tables.

Your inputdata looks like this:

You could resume your data using Pivot Tables, and group the data by that 4th column and sum values in 3rd column. Something like this:

This way you could create a new datarange, where you have grouped your data as you wish, excluding innecesary rows. Try it!

score 0 · Answer 3 · answered Oct 26 '18 at 01:19

Work from the bottom up if you are going to delete rows. See if there is a match to the value in column D above the row you are working on. If there is a match, sum the values in column C into the matched row and remove the row you're working on.

Sub words()
    Dim i As Long, m As Variant

    With Worksheets("sheet1")

        For i = .Cells(.Rows.Count, "D").End(xlUp).Row To 2 Step -1
            m = Application.Match(.Cells(i, "D").Value, .Range("D:D").Resize(i - 1, 1), 0)
            If Not IsError(m) Then
                .Cells(m, "C") = .Cells(m, "C").Value2 + .Cells(i, "C").Value2
                .Cells(i, "D").EntireRow.Delete
            End If
        Next i

    End With
End Sub

Duplicate in column

3 Answers3