none
How do I find duplicate rows based on the value in the first column and make one row from them and add the values of the third column. RRS feed

  • Question

  • How do I find duplicate rows based on the value in the first column and make one row from them and add the values of the third column.

    example: I need all the same rows 1111 to be one row and the 3 column added together 1111 77 $530 qqq. This is going to be used for a large sheet of data of multiple rows. I have the code to make one master sheet from multiple sheets. I just need this last piece to make it fully functional. See the macro code below.

    row1     row2    row3  row4

    1111    77       $200     qqq.

    2311    43       $234     red  

    2424    44       $123     des

    1111    77       $230     qqq

    1111    77       $100     qqq

    code:

    Sub LoopThroughDirectory()
     Dim MyFile As String
     Dim erow
     Dim Filepath As String
     Filepath = "Directory\"
     MyFile = Dir(Filepath)
     Do While Len(MyFile) > 0
         If MyFile = "zfile.xlsm" Then
         Exit Sub
         End If
        
         Workbooks.Open (Filepath & MyFile)
         Range("A2:D20000").Copy
         ActiveWorkbook.Close
        
         erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
         ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
        
         MyFile = Dir

      Loop
     End Sub


    • Edited by VBShaper Tuesday, January 31, 2017 4:01 AM
    Tuesday, January 31, 2017 4:01 AM

Answers

  • Hi,

    We could use WorksheetFunction.SumIf Method (Excel) to sum the column 3 based on column 1.

    E.g.

     

    Set Rng = Range("A2:A7")

    Set sumRng = Range("C2:C7")

    For Each cell In Range("A2:A7")

    rsl = Application.WorksheetFunction.SumIf(Rng, cell, sumRng)

    cell.Offset(0, 5).Value = cell.Value

    cell.Offset(0, 6).Value = cell.Offset(0, 1).Value

    cell.Offset(0, 7).Value = rsl

    cell.Offset(0, 8).Value = cell.Offset(0, 3).Value

    Next cell

     

    We could use Range.RemoveDuplicates Method (Excel) to remove duplicated rows.

    E.g.

    Range("F2:I7").RemoveDuplicates Columns:=Array(1, 3)

    If you have any issue, please feel free to let us know.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by VBShaper Wednesday, February 1, 2017 11:44 AM
    Wednesday, February 1, 2017 7:34 AM
    Moderator

All replies

  • Because You have posted VBA code then I assume that you are reasonably competent with VBA code so how about I just give you guidelines of how to do this. You can record code to get the syntax if you want it to be automated.

    Refer to the following screen capture together with the guidelines below. (The screen capture already has the duplicates removed as per the guidelines below.)

    • Copy the full range of data (columns A:D) to a new location (F:I)
    • Select the new pasted columns (F:I) (probably are still selected following the paste)
    • Select Data ribbon -> Remove duplicates.
    • In the dialog box:  Check box against "My data has headers" (assuming you have column headers otherwise uncheck)
    • Under columns, the first column to be checked and others unchecked.
    • Click OK and you will have a unique list based on the first column of data.
    • Referring to screen capture above, Insert the following formula in cell H2.

             =SUMIF($A:$A,F2,$C:$C)

    • Copy the formula down the column. You can then use Copy -> PasteSpeial -> Values if you wish to remove the formulas after which you can then delete columns A:E if you wish.

    As per my previous comment you can record code to get syntax for VBA code and if you then have problems with the VBA code then post what you recorded and I will assist further.


    Regards, OssieMac


    Tuesday, January 31, 2017 7:01 AM
  • To Celeste Li,

    Don't you think that it would be common courtesy to give the OP a chance to respond before proposing the post as an answer. IMHO it is ridiculous marking the post as proposed answer only 31 minutes after posting. You should wait at least 24 hours before making it.


    Regards, OssieMac

    Tuesday, January 31, 2017 8:47 AM
  • I am aware of the process of manually removing duplicates. I was looking to automate it with VBA. The code I provided is code used to transfer data from multiple workbooks single sheet to a master workbook single sheet. The issue is I get duplicates. I need assistance creating the macro. I m not certain how to start.
    Tuesday, January 31, 2017 12:12 PM
  • To Celeste Li,

    Don't you think that it would be common courtesy to give the OP a chance to respond before proposing the post as an answer. IMHO it is ridiculous marking the post as proposed answer only 31 minutes after posting. You should wait at least 24 hours before making it.


    Regards, OssieMac


    Please, I would like to see your answer. I am looking to automat the process during the transfer of the data from multiple workbooks single sheet to a master workbook single sheet. Less duplicates and adding the price column.
    Tuesday, January 31, 2017 12:20 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    Consolidate 3 sheets, remove duplicates, add select numbers.
    http://www.mediafire.com/file/wid05dyvqrpj7d8/01_31_17.xlsx

    Tuesday, January 31, 2017 6:10 PM
  •  I need assistance creating the macro. I m not certain how to start.
    I have told you how to start as per my guidelines where I said to record the macro to get the syntax and if you still have a problems then post the recorded macro and I will help further.

    Regards, OssieMac

    Tuesday, January 31, 2017 8:06 PM
  • Hi,

    We could use WorksheetFunction.SumIf Method (Excel) to sum the column 3 based on column 1.

    E.g.

     

    Set Rng = Range("A2:A7")

    Set sumRng = Range("C2:C7")

    For Each cell In Range("A2:A7")

    rsl = Application.WorksheetFunction.SumIf(Rng, cell, sumRng)

    cell.Offset(0, 5).Value = cell.Value

    cell.Offset(0, 6).Value = cell.Offset(0, 1).Value

    cell.Offset(0, 7).Value = rsl

    cell.Offset(0, 8).Value = cell.Offset(0, 3).Value

    Next cell

     

    We could use Range.RemoveDuplicates Method (Excel) to remove duplicated rows.

    E.g.

    Range("F2:I7").RemoveDuplicates Columns:=Array(1, 3)

    If you have any issue, please feel free to let us know.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by VBShaper Wednesday, February 1, 2017 11:44 AM
    Wednesday, February 1, 2017 7:34 AM
    Moderator