Excel experts; join firstname + lastname to make a username?

[Scam]

Thought this might as well go here...

If I have column A with first name (say; John) and column B with last name (say; Smith) I want to create a username for them which would be jsmith. Can I automate this in Excel?

Would be nice if I can force to lowercase too...

Thanks!

 

[Scam]

Thanks Bug One and Avenged7Fold! Next question, how come when I drag down from the bottom-right corner it duplicates the answer from my first row down, rather than amending the formula for each row? I'm also having the problem with a formula that takes values from another sheet;

='Sheet 1'!B11

Just a thought, and you may have this covered, but bear in mind that Jackie Smith and John Smith will both end up with the username jsmith. You may need to check that the username is already taken first, and if so, generate an alternative.

Yup this will be an issue.

Basically what I'm doing is creating a nice looking template to give to our partners to fill in employees names/details, then on my second sheet taking the essential values to create a comma delimited CSV that we can hand over to our vendor for mass-import. Kinda fun

 

[Scam]

Perhaps do the formula then drag down, rather than inputting in the names

Doesn't work

What problem are you having with that other sheet formula?

I dont think you need/want the ' there so i guess =Sheet1!B11

As for the drag down issue, that is odd because my excel drags it down fine

The ''s are there because I've named my sheet something (but called it Sheet 1 on here because it contains my company name )

I'm sure I used to be able to take any formula that referenced another cell, and drag it down to make that formula work with other cells It's definitely not working.

Mind you, if I don't know how many rows there are going to be on the first sheet -- how can I make that formula work? If I duplicate the formula for 20 rows, but then it ends up with 25 rows on the first sheet -- I won't get those last 5 rows. Hmn..

EDIT: Got it working. Had a "calculate sheets" set to manual rather than automatically. Yay.

But my last question stands.. how do I apply the formula on the second sheet for however-many rows there are on the first?

 

[Scam]

Try highlighting all the cells and then using CTRL+D, this will copy down the formula in the top cell (the working formula)

Might have some success with that


Also for your other issue just drag down to row 1000 or something like that and it'll fill in the details as they're added. Otherwise you'd need to convert your dataset to a "Table" and then the formula *should* auto copy down.

Ooh it didn't like ctrl+d Crash-fail!

I'll stick with just dragging down the rows for now. This is all WIP anyway

 

[Scam]

Should someone without basic Excel knowledge be in charge of this?

It's not really a big a deal as it sounds. There's going to be about 150 users. We're the customer giving our names to a vendor to put them into their platform. We could just give them a list that does nothing and make them deal with it, but seen as they now accept CSV and have given me a template for batch import I thought I'd try having a 'nice' sheet to work with, then put the data automatically into their template. They're across any issues of duplicates etc, that's their problem

Going forward we'd like to use it for more batch imports.

 

[Scam]

the good ones ask what and why and how the data will be used to do what the requestor actually needs them to do.

The vendor will, they're very hands on with what we're doing here It's a collaboration.

As a side note I'd always use email addresses as a unique identifier if possible when working across multiple customers because sooner or later you're going to come across a clash.

Yep, email addresses are integral to the vendor's system as a unique identifier.