Assign uniqiue Group ID for each groupname (not using case/if)

[mickeb]

Hello i got a issue with converting groups in a dataset into numbers.. or maybe its called assign a group id.

So if example: (i have group name but not group ID)

Name		Group		Value	GroupID
Flower 1	FlowerGroupX	400	1
Flower 2	FlowerGroupB	200	2
Flower 3	FlowerGroupA	500	3
Flower 4	FlowerGroupX	200	1
Flower 5	FlowerGroupB	300	2
Flower 6	FlowerGroupY	500	4
Flower 7	FlowerGroupC	600	5
Flower 8	FlowerGroupX	100	1
Flower 9	FlowerGroupA	400	3
Flower 10	FlowerGroupY	200	4

I understand that you can make a case statement for each groupname and assign a number that way but if i want to make a vba code more dynamic (e.g put a new group input, will produce not wished results..) Im looking for a subrutine

So my question is if there is a way to make so kind of count of unique names in the group. In the example, there is 5 unique groups and then simply the group order is from the starting row and down.

But the number order doesnt matter just need convert same unique string variables uniqiue numbers.

Been searching for a while but no answer

I attach a excel file with same example but all variables exept group id is on input sheet and group id is on output sheet.

here is the excel
http://dl.dropbox.com/u/8387406/Book4.xlsx

Best regards
Mike

 

[AlphaFrog]

Excel Workbook
	A	B	C	D
1	Name	Group	Value	GroupID
2	Flower 1	FlowerGroupX	400	1
3	Flower 2	FlowerGroupB	200	2
4	Flower 3	FlowerGroupA	500	3
5	Flower 4	FlowerGroupX	200	1
6	Flower 5	FlowerGroupB	300	2
7	Flower 6	FlowerGroupY	500	4
8	Flower 7	FlowerGroupC	600	5
9	Flower 8	FlowerGroupX	100	1
10	Flower 9	FlowerGroupA	400	3
11	Flower 10	FlowerGroupY	200	4
...

Cell Formulas
Range	Formula
D2	=IF(COUNTIF($B$1:B2,B2)=1,MAX($D$1:D1)+1,INDEX($D$1:D1,MATCH(B2,$B$1:B1,0)))
D3	=IF(COUNTIF($B$1:B3,B3)=1,MAX($D$1:D2)+1,INDEX($D$1:D2,MATCH(B3,$B$1:B2,0)))
D4	=IF(COUNTIF($B$1:B4,B4)=1,MAX($D$1:D3)+1,INDEX($D$1:D3,MATCH(B4,$B$1:B3,0)))
D5	=IF(COUNTIF($B$1:B5,B5)=1,MAX($D$1:D4)+1,INDEX($D$1:D4,MATCH(B5,$B$1:B4,0)))
D6	=IF(COUNTIF($B$1:B6,B6)=1,MAX($D$1:D5)+1,INDEX($D$1:D5,MATCH(B6,$B$1:B5,0)))
D7	=IF(COUNTIF($B$1:B7,B7)=1,MAX($D$1:D6)+1,INDEX($D$1:D6,MATCH(B7,$B$1:B6,0)))
D8	=IF(COUNTIF($B$1:B8,B8)=1,MAX($D$1:D7)+1,INDEX($D$1:D7,MATCH(B8,$B$1:B7,0)))
D9	=IF(COUNTIF($B$1:B9,B9)=1,MAX($D$1:D8)+1,INDEX($D$1:D8,MATCH(B9,$B$1:B8,0)))
D10	=IF(COUNTIF($B$1:B10,B10)=1,MAX($D$1:D9)+1,INDEX($D$1:D9,MATCH(B10,$B$1:B9,0)))
D11	=IF(COUNTIF($B$1:B11,B11)=1,MAX($D$1:D10)+1,INDEX($D$1:D10,MATCH(B11,$B$1:B10,0)))

 

[mickeb]

I found this formula but how would i implement it into a subrutine?

=IFERROR(VLOOKUP($A2,$A$1:$B1,2,0),MAX($B$1:$B1)+1)

 

[mickeb]

Thanks AlphaFrog! Works well will try to implement it into a subrutine

How would the best attempt be to make it more dynamic in VBA?
e.g formulaRC or?

 

[mickeb]

Sorry if its not to much, i mean how would i make the output static? e.g the cell just show the number not the formula, i dont know if its some kind of vba calculation or?

 

[AlphaFrog]

Here's one way...

Sub Macro1()
    
    Dim Lastrow As Long
    Lastrow = Range("C" & Rows.Count).End(xlUp).Row
    With Range("D2:D" & Lastrow)
        .FormulaR1C1 = "=IF(COUNTIF(R1C2:RC[-2],RC[-2])=1,MAX(R1C4:R[-1]C)+1,INDEX(R1C4:R[-1]C,MATCH(RC[-2],R1C2:R[-1]C[-2],0)))"
        .Value = .Value
    End With
    
End Sub

 

[mickeb]

Thanks for the soloution Works great!