+ Reply to Thread
Results 1 to 10 of 10

Need to identify duplicate values in multiple columns, concatenate and then delete

  1. #1
    Registered User
    Join Date
    09-22-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Need to identify duplicate values in multiple columns, concatenate and then delete

    Hi all,

    Would anyone be able to assist with providing an Excel macro to include multiple column ranges for duplicates vs. just column A, then concatenate and delete?

    For example, I need the macro to look at columns A-D to determine if it is a true duplicate before concatenating and deleting it.

    I've already tried a macro that I found in the thread below, but it only looks at a single column - and I need to verify duplicates over four columns.

    https://mandrillapp.com/track/click/...YTMzYVwiXX0ifQ

    Thank you in advance for your assistance with this request. I look forward to your response.

    Sincerely,
    Allison

  2. #2
    Registered User
    Join Date
    09-10-2014
    Location
    Matrix
    MS-Off Ver
    2010
    Posts
    70

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    An attachment please.
    (*) Reputation points appreciated.
    excelbs.tk

  3. #3
    Registered User
    Join Date
    09-22-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    I've uploaded an attachment, per your request, as an example of the data that needs to review the first four columns as true duplicates, then concatenate and delete the remaining fields.

    The attachment was uploaded using Manage Attachments. Please let me know if there is anything further you need.

    Thanks!
    Allison
    Attached Files Attached Files

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    Try this:-
    Duplicate rows based on columns "A to D" deleted.
    Please Login or Register  to view this content.
    Regards Mick

  5. #5
    Registered User
    Join Date
    09-22-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    Thank you for the help. I appreciate it! It does verify the 4 columns like I need, however, it is not concatenating the duplicate information - it only deletes it. I need to keep the data, but apply it to one record.

    For example, the macro below will concatenate duplicates, then delete the duplicates - only it searches duplicates in one column. Could the same be done while searching four columns?

    Option Explicit

    Sub Consolidate()
    'JBeaucaire (9/18/2009)
    'Sort/Match column A values, merge all other cells into row format
    Dim LR As Long, i As Long
    Application.ScreenUpdating = False

    'Sort data
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess

    'Group matching names
    For i = LR To 2 Step -1
    If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
    Range(Cells(i, "B"), Cells(i, Columns.Count).End(xlToLeft)).Copy _
    Cells(i - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
    Rows(i).EntireRow.Delete (xlShiftUp)
    End If
    Next i

    Cells.Columns.AutoFit
    Application.ScreenUpdating = True
    End Sub

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    I can see what the code does, it deletes the duplicate row and places a copy of it in the next cell to the right of the original
    I'm not sure how you want 4 columns displayed!!!
    Please show an example of just a pair of duplicate lines (4 columns) and the expected result.

  7. #7
    Registered User
    Join Date
    09-22-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    Again, thank you so much for your help. B/c I'm at a loss right now.

    Attached are examples of the data, with more info. Example 2 is the file with more data. Example 3 is after using the first macro.

    If you compare Example 2 w/ 3, and scroll to the bottom of 3, you'll see where it concatenates Private Address (column A) - all of them. Problem with this macro, it doesn't check columns B-D to confirm if this really is a duplicate, vs. different people with a similar address.

    Would need the right macro to look at four unique identifiers before running concatenation and deletion - look at the first four columns vs. only the first one to determine if it is a true duplicate, then concatenate the entire rest of the data. It will end up being a ton of extra columns, but that's what is needed for this work project.

    Let me know if you need any more information to help solve. Thank you so much!
    Attached Files Attached Files

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    Try this:-
    Lightly Checked !!!
    Please Login or Register  to view this content.
    Regards Mick

  9. #9
    Registered User
    Join Date
    09-22-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    6

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    It worked! You are wonderful! I sincerely cannot thank you enough. Weeks spent trying to figure this out - will totally go to this forum first from now on.

    You are a gem!
    Allison

  10. #10
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Need to identify duplicate values in multiple columns, concatenate and then delete

    You're welcome
    Glad it's working
    Regrds Mick

+ 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] Need to find if duplicate values exist in a column, concatenate cells and then delete
    By Siemieniuk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-22-2017, 07:22 PM
  2. [SOLVED] identify duplicate values in multiple columns
    By p99 in forum Excel General
    Replies: 6
    Last Post: 08-12-2016, 05:28 PM
  3. Delete Entire Row on duplicate values from multiple columns
    By Dahlia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-16-2015, 08:26 PM
  4. Delete values of duplicate rows only in specific columns
    By DeeBee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2013, 09:43 AM
  5. Replies: 6
    Last Post: 03-04-2013, 12:03 AM
  6. Replies: 0
    Last Post: 11-15-2012, 06:47 AM
  7. Replies: 0
    Last Post: 06-12-2012, 02:00 PM

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