[Solved] VBA: assigning unique IDs where names appear n time

  • Hi,


    I have a huge s/sheet with 25,000 rows, and 50+ columns. Each " company name" (row) has 50+ "characteristics" (columns) and some company names appear many times, with different characteristics.


    Does anybody have any suggestions how I can:


    1)Assign each company name a unique ID, and where that appears more than once, give it the same ID?


    2)Create an additional column that reports the number of times that name appears (number of rows)?


    An example would be:


    ID name - #appearances - characteristics
    1 nameA - 1 - 1 0 0 0 (etc)
    2 nameB - 3 - 1 1 0 1 0
    2 nameB - 3 - 1 0 1 0 0
    2 nameB - 3 - 0 1 0 0 0
    3 nameC - 2 - 1 1 0 0 0
    3 nameC - 2 - 1 1 0 0 1
    4 nameD - 1 - 1 1 0 0 0


    ---
    Windows ME/ Office 2000
    Or: Windows XP/ Office XP



    Thanks in advance for any/all help.


    Cheers,


    Mike

  • Wow, Thanks Bill!


    That solution works perfectly. You don't want to know how many filters/subtotals I'd applied to try to get there, and with 30,000 rows it takes a whole heap of time!


    Thanks again.


    Mike

Participate now!

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