I have attached a sample spreadsheet. I'm looking for a macro that will take column one which contains Company names which are sometimes duplicated and append an incremental number based on the number of occurrences to the end of the company forcing it to be unique. Column 2 has the example of the results I would like to see. Has anyone got a macro that I could use that would allow me to select the company column, run the macro, and it update the values in that column to append the incremental number?
Last edited by aharmon426; 10-13-2009 at 09:12 PM.
I put this formula in b1 and copied down the column (This works as long as column A is sorted alphabetically to group common names together)
Code:=IF(AND(A2=A1,ISERROR(RIGHT(B1,2)+1)),A2&" 2",IF(A2=A1,A2&(RIGHT(B1,2)+1),A2))
I help because of the Pavlovian dog that resides in the inner me...so if you are happy with the results, please add to my reputation by clicking the blue scales icon in the upper right portion of the blue bar of the post. It helps keep me motivated!
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
Use this formula in B2 and copy down the column B. It should work regardless whether column A is sorted or not.
TonyCode:=IF(COUNTIF($A$2:A2,A2)>1,A2&" "&COUNTIF($A$2:A2,A2),A2)
Thanks for the quick responses.. I mean really quick. I have tested both solutions and they work. You both have saved me a great deal of time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks