+ Reply to Thread
Results 1 to 5 of 5

Moving duplicate data into new columns

  1. #1
    Registered User
    Join Date
    12-02-2008
    Location
    PA
    Posts
    2

    Moving duplicate data into new columns

    Hello Everyone,

    I've got a spreadsheet where I have duplicate data in column A but unique data in column B. What I'm attempting to do is move the unique data in column B into new columns where there is a duplicate in column A. Very confusing to state but examples are great things.

    Currently:
    A B
    3100377 25602010
    3100377 25602213
    3100377 25602310

    What I need it to look like:
    A B C D
    3100377 25602010 25602213 25602310

    Anybody know a way of doing this?
    Last edited by VBA Noob; 12-02-2008 at 01:59 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    like this you need to create an unique list from col a of your sheet(use data/filter /advanced filter/copy to another location /uniqe records only
    and put that in col c then use the array formula as in cell d1 confirmed with ctrl+shift+enter then dragged across as far as you want and down to the end of your unique data.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-02-2008
    Location
    PA
    Posts
    2
    You are the best!!! The only issue I had with the formula is it stopped working about the 2000th line so I changed it from that to 5000 throughout the formula and then received errors and couldn't figure it out. So I took the easy way out and cut out the data for rows 1-2000 and then repeated it until the formula worked for the remaining rows. This is a huge help to the project I am working on so thank you thank you thank you!!

  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
    When you edited the formula, you just pressed ENTER when you were done. That's what broke the formula. Martin indicated these were array formulas and as such you have to press CTRL-SHIFT-ENTER to confirm them. Just ENTER won't work.

    You know the array is active if you then see little braces { } are now added around your formula.
    _________________
    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!)

  5. #5
    Registered User
    Join Date
    06-09-2014
    Posts
    2

    Re: Moving duplicate data into new columns

    Love it when years old questions still give results. Just save much time with this solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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