+ Reply to Thread
Results 1 to 6 of 6

Removing duplicate entries while preserving their structure

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    5

    Removing duplicate entries while preserving their structure

    Given a data set like this:

    5 10
    5 7
    5 13
    7 5
    7 13
    10 13
    10 5
    13 7
    13 10
    13 5

    How can I format the data into something like the following?

    5 10,7,13
    7 5,13
    10 13,5
    13 7,10,5

    The commas could be spaces, or the numbers could be spread out among the columns.

    What I need to do is remove all duplicate entries in column 1, while pasting all of their corresponding column two values into a single row. Is there a function that does this, or a relatively easy way to program it? I appreciate any help with this.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing duplicate entries while preserving their structure

    MERGE ROWS
    Here's a macro for merging rows of data using the ID in column A and putting the column B values together in a delimited manner of your choosing. There's a sample file as well for testing.There's a sample workbook too you could drop your data into and test it out.
    Last edited by JBeaucaire; 12-26-2019 at 05:23 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Removing duplicate entries while preserving their structure

    Thanks! It doesn't seem to work with large datasets, like what I have here. Is there a remedy to this problem?

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing duplicate entries while preserving their structure

    Try using a delimiter other than a comma when concatenating all numeric values. Excel thinks it's helping when it restructures numeric strings with commas... use a space or a | and see if it works better for you.

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Removing duplicate entries while preserving their structure

    I've tried several delimiters, and to no avail. The output after running the macro is the first row of data, and nothing else. Excel doesn't like the size of the data. For 100,000 rows, I have the same problem. When I run it on 50,000 rows, however, it runs and gives me the right output.
    Last edited by Lagrange17; 07-22-2014 at 09:06 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing duplicate entries while preserving their structure

    Ah, yes, that makes sense. Even though my macro will eliminate duplicate values, there is a limit to the length of a text string in a single cell, I'm certain of that, though not certain of the effects that has since I've never tested my macro with anything close to that much data.

+ 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. removing duplicate entries
    By armagan in forum Excel General
    Replies: 3
    Last Post: 01-02-2014, 06:27 PM
  2. Removing BOTH duplicate entries
    By hfswjyr in forum Excel General
    Replies: 5
    Last Post: 12-17-2008, 05:12 AM
  3. removing duplicate entries
    By whoggard in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 09-07-2007, 05:42 PM
  4. Removing duplicate entries
    By katiek648 in forum Excel General
    Replies: 2
    Last Post: 08-04-2006, 02:00 PM
  5. Removing duplicate entries.
    By dh01uk in forum Excel General
    Replies: 2
    Last Post: 01-13-2006, 11:55 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