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

If you want a very easy/lazy way to ensure you don't have any duplicates then you could add the row number to the end of it.

Otherwise you could use a countif referencing the cells above the current username and add 1 to it.
 
As above, If you have several multiples you might want to to tove an interim countifs column and automatically add anything counting greater than one (lock the first cell of the range then drag down) to the end of the username.

Do everything like this in row /column steps and it's much easier for you to see if you've done something wrong/ silly and tbh much easier for someone else to follow down the line.

Depending on the size of the dataset and the rest of the sheet you might be better running it through a macro but it sounds unnecessary here
 
chroniclard said:
Should someone without basic Excel knowledge be in charge of this? :p
Click to expand...
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.
 
As above, the "lower left &" formula is the one you want.

If it's putting "jsmith" in every cell, if that's the first username, it could be that your sheet is set to manual calculation.

Try pressing F9 to force a recalc and see if that changes them. If it does you can set it to auto recalc in options.
 
Scam said:
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.
Click to expand...

It's not really the point that "that's their problem", using Excel is ok, it'll get you so far, but you need to think about the implications of things you're doing and solve any issues before they arise, that'll get you further than knowing how to do some formulas.

The rubbish users of data extraction/manipulation/reporting will do what someone asks, exactly as they asked, the good ones ask what and why and how the data will be used to do what the requestor actually needs them to do.
 
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.
 
wesimmo said:
the good ones ask what and why and how the data will be used to do what the requestor actually needs them to do.
Click to expand...
The vendor will, they're very hands on with what we're doing here :) It's a collaboration.

Narj said:
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.
Click to expand...
Yep, email addresses are integral to the vendor's system as a unique identifier.
 
Scam said:
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 :)
Click to expand...

If the data is being is being imported into a database, then things like username creation should be done on the database side during the import rather than in the source. The source isn't aware of what data or usernames already exist within the system, whereas if you do it database side you have access to all that information as well as a responsibility to protect the integrity of the system.

If you really need to specify a username column in the import source, you should use something guaranteed to be naturally unique, such as employee number.
 
You must log in or register to reply here.
Back
Top Bottom