Formula to look for matching numbers & add -1, -2, -3 respectively

[matrixpom]

Hi all,

Does anyone know of a formula I can put into a column that will add -1, when the same number is entered & -2 when the number is entered again & so on?
e.g.

1
2
3
1-1
4
5
1-2
6
2-1

As always thanks for any assistance guys - Marco

[David A Coop]

Do you want this done with a macro, or with a formula in an adjacent column?

=IF(COUNTIF($A$1:A1,A1)-1=0,A1,A1&"-"&COUNTIF($A$1:A1,A1)-1) will give you the results you want if you enter the formula in Column B and copied down.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[matrixpom]

Hi David,

working great but on the next blank cell in Column A it repeats "--1" in column B instead of leaving it blank? Thank you, really appreciate your help David! - Marco

[David A Coop]

Hi Marco,

All you need is an extra test for a BLANK cell, like this:

=IF(A1="","",IF(COUNTIF($A$1:A1,A1)-1=0,A1,A1&"-"&COUNTIF($A$1:A1,A1)-1))


Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[matrixpom]

Hi David,
Works like a charm, thanks for your help! - Marco

[David A Coop]

You're welcome Marco.

DAC