column duplicates / merge data formula and/or macro???

  • I am working with a database that contains the following information

    (This the the data array)


    In the name column, there are duplicate names.

    In the "type" column, they're are multiple types:


    My dilema is, I'm trying to create a formula so that the following happens.

    I want to have Excel sort by "type," then transition the "numbers" from the original column "type" to new columns equal to their "type"


    (Original Data)
    Jeff | 1234567 | HOME
    John | 7654321 | WORK
    John | 8765432 | HOME

    (After Macro or Formula or Both) I'd also assume on "Sheet Two"

    Jeff |............|............|1234567 |.............|...........|............|............|
    John |............|............|8765432 |7654321 |...........|............|............|

    As you can see the formula, and/or Macro automattically determines three things:

    1. It locates the data from column 2 and places it into the proper column whereas the data in column 3 = header row of column(s) 4-8.

    2. If the "type" data matches the header text, the data in column 2 moves to the the correct column.

    3. If there is a duplicate name in column 1, the formula will enter the data into the proper column, but will enter the data into the topmost duplicate named row, and then when all data from column 2 is moved, it will delete the extra duplicate rows.

    I'm going crazy trying to get this done so that excel can automate this time consuming process.

    I get lists 600+ and I need to reorder them according to the above format, it
    would be extremely helpful if one the gurus on this site could help me with some general or advanced formulas!


  • Re: column duplicates / merge data formula and/or macro???

    Will this work?

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • Re: column duplicates / merge data formula and/or macro???

    I have a spread sheet where in column A1 I have duplicate numbers. To the right I have the associated components of that number and need to be left unsorted. My goal is to create a macro that will take all the duplicates merge them and all other columns are left as is. This spread sheet will be used for a mail merge data base to fill out forms. On the forms I will have fields that will be filled out by the unique numbers in column A1 and in the body of the form the data to the left of Column A1 will be placed in the body of the form. Is there anyone that can help me?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!