Which way to skin this cat?

  • Thread starter Thread starter inspireme
  • Start date Start date
I

inspireme

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 :eek: : 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_________________ (e-mail address removed)
2_________________ (e-mail address removed)
3_________________ (e-mail address removed)
4_________________ (e-mail address removed)
5_________________ (e-mail address removed)
6_________________ (e-mail address removed)
7_________________ (e-mail address removed)
8_________________ (e-mail address removed)


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_________________ (e-mail address removed)
2_________________ (e-mail address removed)
3_________________ (e-mail address removed)
4_________________ (e-mail address removed)
5_________________ (e-mail address removed)
6_________________ (e-mail address removed)
7_________________ (e-mail address removed)
8_________________ (e-mail address removed)


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

MEMBER ID*_______*USERNAME*______EMAIL ADDRESS
1_________________ (e-mail address removed)
2_________________ (e-mail address removed)
3_________________ (e-mail address removed)
4_________________ (e-mail address removed)
5_________________ (e-mail address removed)
6_________________ (e-mail address removed)
7_________________ (e-mail address removed)
8_________________ (e-mail address removed)

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

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 :eek: : 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_________________ (e-mail address removed)
2_________________ (e-mail address removed)
3_________________ (e-mail address removed)
4_________________ (e-mail address removed)
5_________________ (e-mail address removed)
6_________________ (e-mail address removed)
7_________________ (e-mail address removed)
8_________________ (e-mail address removed)


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_________________ (e-mail address removed)
2_________________ (e-mail address removed)
3_________________ (e-mail address removed)
4_________________ (e-mail address removed)
5_________________ (e-mail address removed)
6_________________ (e-mail address removed)
7_________________ (e-mail address removed)
8_________________ (e-mail address removed)


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

MEMBER ID*_______*USERNAME*______EMAIL ADDRESS
1_________________ (e-mail address removed)
2_________________ (e-mail address removed)
3_________________ (e-mail address removed)
4_________________ (e-mail address removed)
5_________________ (e-mail address removed)
6_________________ (e-mail address removed)
7_________________ (e-mail address removed)
8_________________ (e-mail address removed)

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
Click to expand...
 
Domenic said:
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
Click to expand...

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

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
 
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.
Click to expand...

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!
Click to expand...

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?
Click to expand...

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?
Click to expand...

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


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

Thanks again!

Best,
-JP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

continuous form 7

Back
Top