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:

BEFO
MEMBER ID[/b]_______*USERNAME*______*EMAIL ADDRESS
1_________________
2_________________
3_________________
4_________________
5_________________
6_________________
7_________________
8_________________


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_________________
2_________________
3_________________
4_________________
5_________________
6_________________
7_________________
8_________________


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_________________
2_________________
3_________________
4_________________
5_________________
6_________________
7_________________
8_________________

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
------------------------------------------------------------------------
inspireme's Profile: http://www.excelforum.com/member.php...o&userid=28209
View this thread: http://www.excelforum.com/showthread...hreadid=477506

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 CopyPaste SpecialValues to remove the
formulas and list just the results. Delete column B (C becomes B) and
export to your MySQL file.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=477506

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
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:

BEFO
MEMBER ID[/b]_______*USERNAME*______*EMAIL ADDRESS
1_________________
2_________________
3_________________
4_________________
5_________________
6_________________
7_________________
8_________________


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_________________
2_________________
3_________________
4_________________
5_________________
6_________________
7_________________
8_________________


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_________________
2_________________
3_________________
4_________________
5_________________
6_________________
7_________________
8_________________

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

Domenic Wrote:
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

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.
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!

Now, looking at the first formula:

1. What value is "B9" and ","") supposed to be and explain why?
2. I don't follow step 2 outlined in the earlier quotes..please help!


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? 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?

Please help!

Thank again!

Best,
-JP


--
inspireme
------------------------------------------------------------------------
inspireme's Profile: http://www.excelforum.com/member.php...o&userid=28209
View this thread: http://www.excelforum.com/showthread...hreadid=477506

In article ,
inspireme
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.

Thanks so much for your reply!

I did find out why I wasn't getting values for the 2nd formula!
Simple!
I forgot the "=" sign


The updates to the 1st formula makes sense now...I'll give it a
whirl...

Thanks again!

Best,
-JP


--
inspireme
------------------------------------------------------------------------
inspireme's Profile: http://www.excelforum.com/member.php...o&userid=28209
View this thread: http://www.excelforum.com/showthread...hreadid=477506