How to rename duplicate rows

Question

I have an Excel sheet with I have around 25k rows of data in the following format:

Column1 | Column 2

A | value1    
A | value2    
A | value3    
B | value1    
B | value2    
C | value1    
C | value2

and so on. I would like to rename the duplicate rows and put an integer in place of it. I would like the new data to be:

Column1 | Column 2

  1 | value1    
  1 | value2    
  1 | value3    
  2 | value1    
  2 | value2    
  3 | value1    
  3 | value2

How can this be done?

Answer 1

Im assuming your Column 1 is in Col A and your Column 2 is in Col B and that you just simply want to convert letters (capital letters) to number.

Insert a new column as Col A. So now your letter are in Col B. Then type the formula

=CODE(B2) - 64

in cell A2 (assuming row 1 is for headings) and drag it down.

Then copy the whole of Col A, right click on cell A1 and choose paste values. Then delete Col B.

---

Based on your comment do the following instead.

Insert a column between the two in you example. In the top cell of the column put the value 1. In the next cell =IF(A2<>A1,B1+1, B1) and then drag down.