Search code examples
sqlexcelfilterexcel-formulabusiness-intelligence

Filter excel table for unique sets

I have an excel table that looks like this: 

Row  Name 
 1   uniqueName001_vid1.mpg
 2   uniqueName001.mpg
 3   uniqueName002_vid1.mpg
 4   uniqueName002_vid2.mpg
 5   uniqueName002.mpg

I am trying to figure out how to identify and flag(give a unique ID) sets within the table that contain the same uniqueName. For instance Row's 1 and 2 would be one set and Row's 3, 4, and 5 would be another set.

My ideal result is this: 

Row  Name                     UID
 1   uniqueName001_vid1.mpg   SET1
 2   uniqueName001.mpg        SET1
 3   uniqueName002_vid1.mpg   SET2
 4   uniqueName002_vid2.mpg   SET2
 5   uniqueName002.mpg        SET2

I can run a SQL query in excel if that is better option than excel formula's too.

Any suggestions are greatly appreciated!

Solution

  • If all starts with uniqueNameXXX than it is easy

    Row Name                    UniqueName      Unique#             UID
1   uniqueName001_vid1.mpg  =LEFT(F4;13)    =IF(G3<>G4;H3+1;H3) ="UID"&H4

    If not, than you should define how to get uniqueName