Which way to skin this cat?

**inspireme** · 10-19-2005, 10:44 AM

Hello All,

Subtitle: "Add numericals values to names within the same field to create unique usernames"

I didn't know quite what to call this process, so its descriptive, so please read examples carefully... here goes:

GOAL

: I want to create unique usernames (no 2 can be the same) for export into a MySQL database from an existing spreadsheet from first names only (no last names avail) that are the same, see BEFORE example below:

PROBLEM

: Many records in the username column have the same or common names but all usernames must be unique to be used in a log-in username for member forum or blog as seen below:

BEFORE:
member ID_______username______email address
1_________________ mark_______________mark@isp.com
2_________________ steve_______________steve@aol.com
3_________________ john_______________jpaul@next.com
4_________________ john_______________john@turkey.com
5_________________ ellen_______________ellen@aol.com
6_________________ chris_______________cwells@isp.com
7_________________ chris_______________chris@money.com
8_________________ chris_______________chris@aol.com

AFTER - METHOD#1 -here I propose to temporarily put username in asending (alphabetical) order select the same names and somehow add a number begining with 1 then 2,3,4..and so on to get the following result seem below:

member ID_______username______email address
1_________________ mark_______________mark@isp.com
2_________________ steve_______________steve@aol.com
3_________________ john1_______________jpaul@next.com
4_________________ john2_______________john@turkey.com
5_________________ ellen_______________ellen@aol.com
6_________________ chris1_______________cwells@isp.com
7_________________ chris2_______________chris@money.com
8_________________ chris3_______________chris@aol.com

AFTER METHOD#2: Somehow combine the username column with the member ID column to create a unique username:

member ID_______username______email address
1_________________ mark1_______________mark@isp.com
2_________________ steve2_______________steve@aol.com
3_________________ john3_______________jpaul@next.com
4_________________ john4_______________john@turkey.com
5_________________ ellen5_______________ellen@aol.com
6_________________ chris6_______________cwells@isp.com
7_________________ chris7_______________chris@money.com
8_________________ chris8_______________chris@aol.com

Please explain step-by-step how to have results similar to the example AFTER METHODS 1 or 2 or both in Excel (Mac preferred) windows O.K..

Any other ideas to skin this cat are welcome!

Thank so much in advance for your help!

Best,

-JP

**swatsp0p** · 10-19-2005, 11:33 AM

Probably the simplest method would be to use a helper column (C?) to concatenate the name and Member ID. Per your example, if the ID is in column A and the first name is in column B, then:

C1 =B1&A1

C1 would return "Mark1". copied down will return:

C2 Steve2
C3 John3
C4 John4.....

This column (C) can then be Copy>Paste Special>Values to remove the formulas and list just the results. Delete column B (C becomes B) and export to your MySQL file.

Good Luck

Domenic · 10-19-2005, 12:05 PM

Assuming that Columns A, B, and C contain your data, and the first row
contains your headers/labels, try...

D2, copied down:

=B2&IF(COUNTIF($B$2:B9,B2)>1,COUNTIF($B$2:B2,B2),"")

or

=B2&ROWS($D$2:D2)

Then, if you want to convert these to values...

1) Select your new column, Column D

2) Edit > Copy > Edit > Paste Special > Values > Ok

Hope this helps!

In article <inspireme.1x5myb_1129734307.5211@excelforum-nospam.com>,
inspireme <inspireme.1x5myb_1129734307.5211@excelforum-nospam.com>
wrote:

> Hello All,
>
> Subtitle: "Add numericals values to names within the same field to
> create unique usernames"
>
> I didn't know quite what to call this process, so its descriptive, so
> please read examples carefully... here goes:
>
> GOAL

: I want to create unique usernames (no 2 can be the same) for
> export into a MySQL database from an existing spreadsheet from first
> names only (no last names avail) that are the same, see BEFORE example
> below:
>
> PROBLEM

: Many records in the username column have the same or
> common names but all usernames must be unique to be used in a log-in
> username for member forum or blog as seen below:
>
> BEFORE:
> MEMBER ID[/B]_______*USERNAME*______*EMAIL ADDRESS
> 1_________________ mark_______________mark@isp.com
> 2_________________ steve_______________steve@aol.com
> 3_________________ john_______________jpaul@next.com
> 4_________________ john_______________john@turkey.com
> 5_________________ ellen_______________ellen@aol.com
> 6_________________ chris_______________cwells@isp.com
> 7_________________ chris_______________chris@money.com
> 8_________________ chris_______________chris@aol.com
>
>
> AFTER - METHOD#1 -here I propose to temporarily put username in
> asending (alphabetical) order select the same names and somehow add a
> number begining with 1 then 2,3,4..and so on to get the following
> result seem below:
>
> MEMBER ID*_______*USERNAME*______*EMAIL ADDRESS
> 1_________________ mark_______________mark@isp.com
> 2_________________ steve_______________steve@aol.com
> 3_________________ john1_______________jpaul@next.com
> 4_________________ john2_______________john@turkey.com
> 5_________________ ellen_______________ellen@aol.com
> 6_________________ chris1_______________cwells@isp.com
> 7_________________ chris2_______________chris@money.com
> 8_________________ chris3_______________chris@aol.com
>
>
> AFTER METHOD#2: Somehow combine the username column with the member ID
> column to create a unique username:
>
> MEMBER ID*_______*USERNAME*______[B]EMAIL ADDRESS
> 1_________________ mark1_______________mark@isp.com
> 2_________________ steve2_______________steve@aol.com
> 3_________________ john3_______________jpaul@next.com
> 4_________________ john4_______________john@turkey.com
> 5_________________ ellen5_______________ellen@aol.com
> 6_________________ chris6_______________cwells@isp.com
> 7_________________ chris7_______________chris@money.com
> 8_________________ chris8_______________chris@aol.com
>
> Please explain step-by-step how to have results similar to the example
> AFTER METHODS 1 or 2 or both in Excel (Mac preferred) windows O.K..
>
> Any other ideas to skin this cat are welcome!
>
> Thank so much in advance for your help!
>
> Best,
>
> -JP

**inspireme** · 10-19-2005, 04:40 PM

Originally Posted by Domenic

Assuming that Columns A, B, and C contain your data, and the first row
contains your headers/labels, try...

D2, copied down:

=B2&IF(COUNTIF($B$2:B9,B2)>1,COUNTIF($B$2:B2,B2),"")

or

=B2&ROWS($D$2:D2)

Then, if you want to convert these to values...

1) Select your new column, Column D

2) Edit > Copy > Edit > Paste Special > Values > Ok

Looking at the first formula:

1. What value is "B9" and ","") supposed to be and explain why?

Thank again!

Best,
-JP

Domenic · 10-19-2005, 06:05 PM

In article <inspireme.1x63md_1129755915.1624@excelforum-nospam.com>,
inspireme <inspireme.1x63md_1129755915.1624@excelforum-nospam.com>
wrote:

> O.K. I a newbie at this...Let's start with the 2nd formula first...If
> I'm getting this right:
>
> 1. I should select a new column D and fill down that column with the
> actual formula.

Yes, enter the formula in D2 and copy down...

=B2&ROWS($D$2:D2)

If you enter the formula in another location, let's say G2, change
ROWS($D$2:D2) to ROWS($G$2:G2).

> 2. Go to Edit menu and copy the entire column D then paste special with
> values selected? I tried this, but I must be missing something here BIG
> TIME!

Yes, that's it. Select your data in Column D and then go through the
steps. If you're still having problems, post back.

> Now, looking at the first formula:
>
> 1. What value is "B9" and ","") supposed to be and explain why?

I was using the range in your example. I assumed A1:C9 contained your
data, and that the first row contains your headers/labels. Actually, B9
should be $B$9. Therefore the formula should be...

=B2&IF(COUNTIF($B$2:$B$9,B2)>1,COUNTIF($B$2:B2,B2),"")

Change the range $B$2:$B$9 according to the data/range contained in your
spreadsheet.

> I'm assuming when you said "D2, copied down:" that meant to copy one
> of the above formulas to D2 and "fill down" so I'd see the formula
> repeated down column D...right?

Right.

>Then I copy, paste special and it
> appears nothing happens?? Where are the new values supposed to appear?
> Is there any operator that should be selected in the "Paste Special"
> Menu?

Once you've entered the formula in Column D, here's what you do...

1) Select the data in your new column, Column D

2) With the data highlighted, select 'Edit' from the 'Menu' and then
'Copy'

3) Then select 'Edit' again, Paste Special, Values, and click Ok

Now the same column, Column D, will contain the actual values. It will
no longer contain the formulas. Post back if you need further help.