CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007worksheet-function
Monodeep
Monodeep

Reputation: 1392

How to rename duplicate rows

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?

Upvotes: 0

Views: 2321

Answers (2)

pnuts
pnuts

Reputation: 59475

Given the confusion, an alternative which I cannot describe in detail because of the ambiguity of the question, in outline is:

Copy ‘Column1’ to a new sheet (when might as well sort alphabetically). Remove Duplicates. Index result and use new sheet as a lookup table.

EDIT (to provide some details now that clarification has been provided):

Copy ColumnB of Sheet Specifications to a new book as ColumnA.
Name tab Table.
Unmerge cells.
Sort alphabetically.
Select ColumnA, Data Tools, Remove Duplicates.
Select first visibly unoccupied row, Ctrl+Shift+Down, Delete.
Save workbook as Table.xls. Close and reopen Table.xls.
Key: in A1 Item#; in A2 1; in A3 =A2+1.
Double click bottom RH corner of A2.
Copy ColumnA to ColumnC.
In Specifications insert a column on the far left.
Key: in A1 Item#; in A2 =VLOOKUP(C2,Table.xls!$B:$C,2,FALSE)
Double click bottom RH corner. Manually adjust #N/A results (merged cells).

Can move Table into same workbook if desired.

Upvotes: 3

Dan
Dan

Reputation: 45752

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.

enter image description here

Upvotes: 2

Related Questions