How to mark multiple entries in the same column with numbers?

Hello,

I have many wordlists, which have multiple entries in one column. I would like to mark up entires which appear more then once with numbers 1,2,3....The number should be put at the end of the word or ath the beginning.

Before Macro:

Column A

Love
Love
Long
Lonely
Lonely
Living
Living


After running the macro:

Column A

Love 1
Love 2
Long
Lonely 1
Lonely 2
Living 1
Living 2

Thank you very much

In B1 and copy down,

=countif(a$1:a1, a1)

Adding to shg's suggestion, using this formula will show the cell as you are intending, but in column B:

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

HTH

Jason

First of all thank you very much for your answer.

I dont know what i am doing wrong. But every time i paste the function in column B it shows me an error. I am sending a smal excell file and i will very appriciate if you could please edit that file and write the funktion in Coloumn B and upload it for me as soon as it functions. Thank you very much in andvance.

First, just copy and paste either of the above two formulas supplied above into cell B1. Then, copy the cell, and paste down through B16.

HTH

Jason

hi,
thanks for the answer. I did the way you asked me to. But an error message comes that the given formula cosist of error.

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

It seems having problem with the red area! Is it possible to upload an example excel file here, where you have given the formula and it funktions?

Originally Posted by wali

Is it possible to upload an example excel file here, where you have given the formula and it funktions?

Yes, you need to ZIP it first, and then post the ZIP file.

Jason

not working yet!! :-(
But thank you very much any way.

i am sorry but its still not working. I dont know, whats wrong. I tried it in excel2003 and in 2007. In both cases i get the error for both ; and , in both excel versions. My excel is in german but i cant imagin that thats the reason for the error.

I am uploading a test file. I will really appriciate, if some one could find the time to add the formula in it and upload it again.

Thanks

You weren't trying to enter the formula in column A, were you? That formula was meant for column B (see attached).

Jason

Thank you very much. Now it works the way it should. It really was the excel language version problem. Now as i opened your excel file the formula was automaticaly translated into german and it looks now like:

=A1&WENN(ZÄHLENWENN(A:A;A1)>1;" "&ZÄHLENWENN(A$1:A1;A1);"")

Thank you very much.