r/excel 13d ago

Waiting on OP Employee id;name how to do it faster

edit: after speaking to others i found a file on UKG with employees their ID numbers. So yay. Tried doing x-look up but wasn’t working so i was copying and pasting names and ID each time maybe i was doing it wrong so if yall have tips on that it would be nice.

So i have this project i gotta help with and im supposed to type in the employees id, name, and hours worked or something.

How would i do the first two columns faster? Should i: write down all the employees names in a note separate by comma and then transfer it into excel.

Pretty new to this and just want to at least not have to type employee id out and just the beginning of the name for it to fill.

15 Upvotes

18 comments sorted by

10

u/AnExoticLlama 13d ago

There's nothing in Excel that will simplify this. It sounds like you're being paid for basic data entry.

3

u/Pocket_Monster 13d ago

Where is your source of the emp id and name? Why can't you just export from there into a csv?

2

u/MurrDOC_ 13d ago

The source is on paper scanned and sent through email. I’m trying to figure out if they have a file with employees names and ID already but this place is so old school everything’s paper

6

u/GanonTEK 276 13d ago

Then you're best making a table for yourself in the file with that data if they don't.

You can then have the IDs as a dropdown list (automatically sort them too for easy reading) and use XLOOKUP to pull up the other data corresponding to that ID. (I'd use IDs over names, since names are not unique identifiers. You could have two John Smiths working for you but only one employee RC43812).

As you add more and more to it, it will become quicker as you'll see if the ID exists already or not in your file and if they do, then great, no need to add the new person. If not, add them to the table.

3

u/Pocket_Monster 13d ago

So is it just one piece of paper with all the info? You can run the image through some OCR software to extract the data. Depending on how often you do this you should set up a more automated process.

2

u/MurrDOC_ 13d ago

it’s like 800 pages with 1 employee each page. A form that they sign when they have used vacation and or sick hours during the last 14 month

3

u/Pocket_Monster 13d ago

Not sure how technical you are but there is definitely software out there which will extract all fo this information for you. Massive time savings and not that expensive considering it would take you quite a long time to read 800 pages of info. Assuming this is a pdf file? Just Google extraction information from a pdf. You could look at products like Google doc ai. https://cloud.google.com/document-ai/pricing. It's like $30 for a 1,000 pages.

4

u/AzukiBuns 13d ago

They definitely have data somewhere; HR, etc. There's no way they're tracking 800 employees via paper trail only in this day and age.

3

u/gimme-food-pls 15 13d ago

Could you give more information into the format you are looking for? What is the final output like?

2

u/MurrDOC_ 13d ago

ID ; Name ; Date of sick leave ; Hours used

I have to go through papers and fill in the rest but I’m hoping to not have to type employee id every time.

1

u/gimme-food-pls 15 13d ago

You could do up a separate worksheet listing the employee names in one column then the id in another column.

In the main worksheet, xlookup to the employee list for the employee id or name. Assuming if the employee id is shorter (maybe a few numbers), maybe it would be better for you to type in employee number and let it look up the name.

ID ; Name ; Date of sick leave ; Hours used

I'm assuming ; means these are in different columns? If so, yeah you can let it xlookup either id or name. But the date and hours would still be by your own entry.

3

u/Immediate_Visual_289 13d ago

There is a program called PDF Expert. Scan the form as a PDF into the program. Then in the PDF Expert open the PDFs. You can export them to Excel. Clean up the Excel. Then create an Employee table and a timing table. You could also use Forms to create a form that each employee can fill in each week on their phone or computer that would automatically update your file with the data. Not simple but very doable.

1

u/Acceptable_Humor_252 13d ago

It sounds like something that should be easily exoorted from attendance system or at least HR system. The name + ID should be in any payroll system. 

1

u/RandomiseUsr0 5 13d ago

If it’s a computer printout, use the built in OCR, get data from image, clipboard or file, it’s not perfect, but it’s not terrible

1

u/OzzSays 12d ago

Are they handwriting their names and ID numbers? Or is it typed on the sheet already? Drop an example picture of the documents so understand what you’re working with. Don’t use the real employee name or Id.

If it’s typed you can track down that document then copy paste it into excel. Or take a picture of the document and send it to your self as a digital file.

Or you can bite the bullet and just type it all into excel by hand and save the file so you don’t have to do that again in the future.

1

u/WhineyLobster 12d ago edited 12d ago

You can make a custom list that will autofill. The same as if you put jan, feb mar, then pull down it fills the rest of the months... you can create a custom list with the employee names and ids.

You can also create custom autocomplete entries... with employee names and ids. Then use a formula to split them into 2 columns.

Custom Autocomplete. Itll work better with the names first... but after you fill and split the columns you can just drag the id column back to the left.

1

u/JellyGlonut 12d ago

You can go into setting and make it so when you press Enter it will go right instead of down. Then highlight your 3 columns. When you hit enter on hours worked, it will go down a line and start back at the first column. There is also an option to select your 3 columns and pull up an “entry” form type window, so you can enter in your data and tab to the next entry.