none
Grouping Duplicates RRS feed

  • Question

  • I have a list containing 4000+ rows which consists of a series of numbers/letters within Column A:

    0001979AB
    0001979AB
    0002618AE
    0002618AE
    0002618AE
    000812240AC
    000812240AC
    000812240AC

    I want to rename the duplicates by group, such as:

    Group 1
    Group 1
    Group 2
    Group 2
    Group 2
    Group 3
    Group 3
    Group 3






    • Edited by Jansk Tuesday, December 6, 2016 7:14 PM
    Tuesday, December 6, 2016 7:12 PM

All replies

  • Hi Jansk

    Juct copy paste Column A to Column C. then remove duplicates in Column C. in D1, type "Group1", and the pull D1 down to the end, where you should see an incremental pattern like Group2, Group3 and so on.

    ant then come to B1 and just do a basic vlookup. e.g. =vlookup(A1,C:D,2,0)


    Dig your well before you are thirsty

    Tuesday, December 6, 2016 7:22 PM
  • Let's assume that the data begin in A1.

    Enter the following formula in B1:

    ="Group "&SUMPRODUCT(1/COUNTIF(A$1:A1,A$1:A1))

    Fill down to the end of the data in column A.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 6, 2016 7:25 PM
  • Hans, your formula worked exactly as I was looking for - thank you! 

    One thing I noticed though, on some of the data it pulled in weird. Rather than a continuous number 1,2,3, it comes through as .9999. In the example below I expected to see it go from Group 279 to 280. 

    14107100AD  Group 279
    14107100AD Group 279
    14107100AD Group 279
    14163780AC Group 279.999999999999

    Do you know why that might be? 

    Tuesday, December 6, 2016 9:23 PM
  • The formula performs some complicated calculations that may result in small rounding errors. It's easy to hide them:

    ="Group "&ROUND(SUMPRODUCT(1/COUNTIF(A$1:A1,A$1:A1)),0)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by ExcelinEfendisi Wednesday, December 7, 2016 5:42 AM
    Tuesday, December 6, 2016 10:56 PM