exel questions

[AL_DA_GREAT]

I recently got a one month job organizing and excelfile. I am working in a hospital so I can't outsource my job.

My job is to take info from one file and put it in another file. Many of these are dates. for example. in the old file it is written 20001125 my job is to wtite a new file where is says 2000-11-25. There are about 20 000 of these dates and I really want to do this in a smart way.

The second parts are many columns with letter combinations. It may say asdfg I write a new document with a number. Can this be automated?

So
nothing = 0 in new document.
qwerty = 1 in new document
asdf = 2 in new document
zxcv = 3 i new document

My new job really stinks but if I could get a computer to do it I could browse the internet 8 hours a day and get 20 dollars an hour for it.

 

[Grisu]

the first date part could be done with the concatenate function

=VERKETTEN(LINKS(A1;4);"-";TEIL(A1;5;2);"-";RECHTS(A1;2))

(blast whoever thought that translating function names was smart)

VERKETTEN -> Concatenate
LINKS -> LEFT
TEIL -> MID
RECHTS -> RIGHT

 

[Souron]

Simmilarly, the second part can be done with IF:

=IF(EXACT(document.xls!sheet1!A1,"nothing"),0,IF(EXACT(document.xls!sheet1!A1,"qwerty"),1,IF(EXACT(document.xls!sheet1!A1,"asdf"),2),IF(EXACT(document.xls!sheet1!A1,"zkcv"),3,"Error")))

I might not have the syntax for linking between files quite right. But you can put that in manually.

 

[AL_DA_GREAT]

Great ideas, how do i make this happen for an entire column?

 

[Souron]

dragging, copy+paste, or a macro script.

 

[warpus]

what idiot is wasting his money hiring you to do this manually?

 

[Zelig]

what idiot is wasting his money hiring you to do this manually?

Most people don't understand computers, payscale with IT stuff it totally messed up.

 

[warpus]

Automating stuff like this is something I do at work.

I'm lead programmer on a project that takes a bunch of excel spreadsheets, processes them, and outputs new spreadsheets. The background is.. it's basically the flow of money from various sources, which has been documented by various agencies in various ways... most of the information coming from spreadsheets generated by some sort of a process at some external agency.

I import incoming spreadsheets manually into an msSQL database (automating this process is possible but would complicate the rest of the process a LOT), then write sever-side code that runs on a website server and goes through the imported files, also connecting with an oracle database which houses student records.. so there's a lot of joins, matching, data changes, computations, etc. that happens and the result is one giant data file that has exactly 1 line per unique person, outputted in an html table. There is code to convert that to .xls or you can just cut and paste it into an excel spreadsheet, where it is passed on to an intern who verifies every single row. Most of them are fine, but there is weird stuff that sticks out, and that has to be investigated manually.

And that's as much as I can tell you without risking certain death. So yeah, that's how I would do it, but that's just cause that's what I've been doing for a couple hours every week for the past 3 months.

edit: and I hate excel and want to minimize time spent using it

 

[Zelig]

Yeah, but you're not getting paid an appropriate amount more than data entry people who do your work by hand.

 

[Mise]

I would just do the whole thing in VBA, but that's because I know how to script in VBA.