+ Reply to Thread
Results 1 to 6 of 6

Merge and delete duplicated

  1. #1
    Registered User
    Join Date
    01-27-2023
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    2

    Merge and delete duplicated

    Hi Guys,

    So I have this huge spreadsheet with over 1k duplicated lines and I need to merge the duplicated information.

    So it's a Table from A to L and from 1 (Header) to 3127 and all the data it's the same except for the column L which I need to merge.

    It kinda looks like this:

    Table.pngTable.png

    And I need to look like this:

    Table2.png

    Pretty much: if column G it's duplicated then concat info on column L and delete the other one so we have no duplicated and kept all the info on column L intact.Example.xlsx
    Last edited by Harry.; 01-27-2023 at 12:15 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Merge and delete duplicated

    Hi Harry and welcome to the Forum,

    First, it is so much easier for us to work from a spreadsheet versus a picture.

    Second, the easy method would probably be to add a formula in the last column to identify the dup and then filter and delete.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    01-27-2023
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    2

    Re: Merge and delete duplicated

    Hi Jeffrey, the thing is I need to keep the info from last column as well (both info). Example attached.

    btw thanks for the welcoming
    Last edited by Harry.; 01-27-2023 at 12:27 PM.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Merge and delete duplicated

    Hi Harry,

    Sorry I missed the part about Column L, so going to have to ask for some help on this one. Hopefully one of our VBA experts will be along soon.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Merge and delete duplicated

    with Power Query

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Merge and delete duplicated

    Try VBA solution:
    PHP Code: 
    Option Explicit
    Sub MERGING
    ()
    Dim lr&, i&, j&, k&, rngarr(1 To 100001 To 12)
    Dim dic As Objectkey
    Set dic 
    CreateObject("Scripting.Dictionary")
    With Sheets("B4")
        
    lr = .Cells(Rows.Count"G").End(xlUp).Row
        rng 
    = .Range("A2:L" lr).Value
        
    For 1 To UBound(rng)
            If 
    Not dic.exists(rng(i7)) Then
                dic
    .Add rng(i7), rng(i12)
            Else
                
    dic(rng(i7)) = dic(rng(i7)) & "; " rng(i12)
            
    End If
        
    Next
        
    For Each key In dic.keys
            k 
    1
            
    For 1 To UBound(rng)
                If 
    rng(i7) = key Then
                    
    For 1 To 11
                        arr
    (kj) = rng(ij)
                    
    Next
                    arr
    (k12) = dic(key)
                    Exit For
                
    End If
            
    Next
        Next
    End With
    With Sheets
    ("After"' change output sheet name to suit
        .Range("A2:L10000").ClearContents
        .Range("A2").Resize(dic.Count, 12).Value = arr
    End With
    End Sub 
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] merge duplicated data across multiple sheets and sum & subtracting (update inventory)
    By abdo meghari in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-02-2021, 04:35 AM
  2. [SOLVED] delete rows contain duplicated data or non duplicated by inputbox
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2020, 04:01 PM
  3. [SOLVED] amend code merge duplicated data
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-25-2020, 05:09 PM
  4. Merge based on columns if there are duplicated data
    By danitzia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2014, 06:18 AM
  5. How to search for duplicated cell, merge rows before sending as an email.
    By EugeneE in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-26-2013, 02:02 AM
  6. Replies: 16
    Last Post: 07-25-2012, 02:03 AM
  7. delete duplicated first row
    By marley&me in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-29-2009, 07:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1