merge/remove duplicate rows but values within column may be unique

  • Hello!


    I'm trying to figure out the logic of merging/deleting duplicate rows in a spreadsheet.


    the first 5 columns are typically the duplicates, but its the columns after that that could be blank....which would make it easier..BUT there may be values in there which are unique..


    what I'm trying to do is to delete duplicate rows and retain the data in each column if there is any, so have a couple of duplicates is OK since I can count the values uniquely, but am trying to get rid of the blanks without losing the values!


    edit: if the values are identical in within the column, then that the duplicate does not need to be kept (updated screenshot)


    this is the code I've been playing with, but it's all kinds of wrong (subscript out range) and it didn't do much of anything!


    many thanks!


    example screenshot of what I'm looking to do:[ATTACH=CONFIG]63643[/ATTACH]


    workbook -forum.ozgrid.com/index.php?attachment/63644/

  • Re: merge/remove duplicate rows but values within column may be unique


    thanks for looking and the feedback, sample workbook posted in original post :)

  • Re: merge/remove duplicate rows but values within column may be unique


    I understand now. Will each unique combination only ever have one site completed or could more than one be shown? I'll look at later if nobody else has jumped in in the meantime.

  • Re: merge/remove duplicate rows but values within column may be unique


    Quote from StephenR;735972

    I understand now. Will each unique combination only ever have one site completed or could more than one be shown? I'll look at later if nobody else has jumped in in the meantime.


    if I understand you correctly, than more than one could be shown. That is, there could be more than 1 unique value in a column (site), in which case there can be duplicate rows with the unique values..


    let me know if I answered your question!


    thanks!

  • Re: merge/remove duplicate rows but values within column may be unique


    Hmm not sure. What I meant was whether 123/bob/builder/12/A could have an entry in e.g. site a and site b or will it only ever be one site?


    Also, will you only have three sites or could you have more in practice?

  • Re: merge/remove duplicate rows but values within column may be unique


    Oh I see what you mean! Yes they can have things going on multiple sites.


    oh there may be more than 3....it would be dynamic, generally every 6 months, I'd be adding new sites and striking out some.
    so it could be site 1,2,3 today but may end up as 1,3,4,5,6..etc later.

  • Re: merge/remove duplicate rows but values within column may be unique


    I've not yet had any time to look at this properly, but it would probably be better if you re-arranged your data so a column each for site, no. of hours and type of work. Would make life easier, if an option.

  • Re: merge/remove duplicate rows but values within column may be unique


    Oh I see what you mean! Yes they can have things going on multiple sites.


    oh there may be more than 3....it would be dynamic, generally every 6 months, I'd be adding new sites and striking out some.
    so it could be site 1,2,3 today but may end up as 1,3,4,5,6..etc later.


    I'll take a look and see if I can re-arrange the data.


    thanks for the tip!

  • Re: merge/remove duplicate rows but values within column may be unique


    OK, this isn't quite what you're after, but as close as I can get today. It adds a sheet for the results. I'll come back as and when I have time, but the last bit is a bit fiddly I think.

Participate now!

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