Changing the name of 100 entries out of a list with 20,000 entries

**benjamings** · 08-04-2010, 01:43 PM

Hi,

I have a list with approx. 20,000 entries. Most of the entries in the list appear 4 times which is what I want. However, there are also about 100 entries, that appear 8, 12 or 20 times. I want to change the names of these additional repeats:
Repeat 1-4 of a specific entry should not be changed. Entry 5-8 should recieve an "A" at the end of their description in column A. If present, entry 9-12 should recieve a "B",...

It is important that the order of the list is not changed.

Is that possible with Excel?

Thanks

**zbor** · 08-04-2010, 01:48 PM

If your data is in A column try this in B:

=A1&SUBSTITUTE(CHAR(64+ROUNDUP(COUNTIF($A$1:A1,A1)/4,0)-1),"@","")

**benjamings** · 08-04-2010, 02:24 PM

Impressive. That worked. Thanks so much.
Is there also a way to change the formula so that instead of adding A,B,C,D it adds W,X,Y,Z?

**contaminated** · 08-04-2010, 02:30 PM

Zbor, Unfortunately your formula won't solve this issue. In your example you have 108 instances of "test". Some of them concatenated with "L", onother ones with "K" ... don't know and with other letters, whereas all of "test"s had to be concatenated with the same letter. But as I understood, OP wants to concatenate single letter for the all values. For example in Column A you have 108 instances of my name (Vusal). So all of them must be concatenated with appropriate letter. For example "D".

Maybe it's not the way of macro writing but nothing another came up to my mind

Please Login or Register  to view this content.

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

**zbor** · 08-04-2010, 06:22 PM

Originally Posted by benjamings

Impressive. That worked. Thanks so much.
Is there also a way to change the formula so that instead of adding A,B,C,D it adds W,X,Y,Z?

Sure... Just play with number 64 in formula and write some bigger one...

For example:

=A1&SUBSTITUTE(CHAR(86+ROUNDUP(COUNTIF($A$1:A1,A1)/4,0)-1),"V","")

Will give you W as first one (also change @ to prior character of W which is V)

Note that there shouldn't be more than 4 instances or after WXYZ you'll get :

Please Login or Register  to view this content.

**zbor** · 08-04-2010, 06:24 PM

contaminated, not every word with same letter...
As I understood:

Repeat 1-4 of a specific entry should not be changed.
Entry 5-8 should recieve an "A" at the end of their description in column A.
If present, entry 9-12 should recieve a "B",...

therefore, every 4: testA, testB, testC, testD etc.

**contaminated** · 08-04-2010, 06:27 PM

Originally Posted by zbor

contaminated, not every word with same letter...
As I understood:

Repeat 1-4 of a specific entry should not be changed.
Entry 5-8 should recieve an "A" at the end of their description in column A.
If present, entry 9-12 should recieve a "B",...

therefore, every 4: testA, testB, testC, testD etc.

you're right. Now I see that... sorry for misunderstanding (((

**benjamings** · 08-04-2010, 06:37 PM

Perfect. Thanks so much.

**benjamings** · 08-04-2010, 06:37 PM

Contaminated, thanks anyway. Hope it didn't cost you too much time :-)

**zbor** · 08-05-2010, 04:27 AM

A little upgrade... Don't know where I was looking...
Instead of changing @ to V you can always use:

=A1&SUBSTITUTE(CHAR(86+ROUNDUP(COUNTIF($A$1:A1;A1)/4;0)-1);CHAR(86);"")

Using same number will remove you a first character...