Counting duplicate occurrences in Excel with incremental letters instead of numbers

1
votes

I'm trying to make a unique ID out of some duplicate amounts I have in a spreadsheet.

Right now I'm using the below formula.

=COUNTIF($A$1:A1,A1)&A1

What the formula is doing is attaching a incremental number by count of duplicate occurrences as in the below example:

  • Column A
  • 295
  • 295

  • 295

  • Column B (Countif formula used)

  • 1295
  • 2295
  • 3295

Which is great but things get tricky and I lose the uniqueness I was hoping to create if my column A has "2295" in row 4 for example:

  • Column A
  • 295
  • 295
  • 295

  • 2295

  • Column B (Countif formula used)

  • 1295
  • 2295 (Dupe)
  • 3295
  • 2295 (Dupe)

What I would like to do is add letters incrementing instead of numbers incrementing as I think that would solve my issue. Note just to think ahead but I think it still logically works just by increasing the letters I have some duplicate occurrences of amounts greater or equal to 25.

  • Column A
  • 295
  • 295
  • 295

  • 2295

  • Column B (Countif formula used)

  • A295
  • B295
  • C295
  • 2295
1
excel-formula
I found the answer, this formula below works nicely. =CHAR(64+COUNTIF($B$2:B2,B2))... Ok just noticed zx8754 answer below is the similar as what I found. Thanks!! – user2426672

1 Answers

0
votes

Try this:

=CHAR(64+COUNTIF($A$1:A1,A1))&A1

=CHAR(65) will give you A