Serious help needed with Excel

August 11, 2009 11:41 AM

Crazy excel help needed... again. Every month I have a monotonous series of tasks to perform in excel and I have exhausted all of the possibilities I know of to make things easier- more details inside.

I have 2 reports (report A and report B for my example) I pull every month, sometimes 2 or 3 times a month that I have to reconcile which takes a couple of hours a day for 1 excel file- I have about 10 to 20 a month and it keeps me from doing my real job.

We are talking around 68 columns with anywhere from 2000 rows+ of data.

Currently, my workflow is such:

Pull reports A and B and add them to the same workbook as different worksheets.

Create a new worksheet, which I use to create a working copy of the combined data.

Delete full rows of data in the working copy based on the values of one column of cells:
This is based on dates, so anything after say 01/01/2006 gets moved into a separate worksheet named "deleted data."

Use vlookup to pull a column of data from worksheet B into the working copy worksheet and then with the data pulled in, concatanate it with another column of data that is already existing in the working copy.

Use vlookup to pull in another column of data from worksheet B to the working worksheet.

There is alot of repetitive work done with find and replace and vlookup with different values.

My questions are as follows:

Can I create macros for these repetitive tasks so that I can say all text A in column c gets changed to new text, and all text b in column c gets changed to new text without doing a find and replace for all variables. There are about 12 columns with 4 different types of data that need to be changed in each column to something new.

Can macros even handle what I am asking of excel, or are they too rigid to effectively manipulate all the data I need changed.

Am I using the right program, or should I be looking at a local copy of a mysql database with a GUI (as I'm not that good of a programmer).

Should I use some other database, such as bento, filemaker pro or even access?

Or, am I just destined to use excel in the same workflows over and over again, pushing that boulder up the hill to have it roll back down again.

Please point me in the right direction (websites with tutorials that monkeys could follow) as I'm not very good with VBA and don't know very much about databases besides the fact that they can hold data and import cvs files- my knowledge base is sad, I know.

posted by TheBones to computers & internet (17 comments total)

Excel macros can achieve anything you can describe in non-abstract terms. I often work with people who want a macro, but can't clearly explain what it is they want to do in the first place.

For example, you say "...so anything after say 01/01/2006 gets moved..."

Why 01/01/2006? Is it always that date? Or is that date arrived at mathematically based on something else? What is that calculation?

Mr. Excel has a great discussion board for VBA macro tasks and can be very helpful. You could also hire someone to do this for you, if you can define your workflow in absolute terms.
posted by odinsdream at 11:50 AM on August 11

For this column, the date will always be the same, anything before 2006 will need to be moved. So at least that's a constant.
posted by TheBones at 11:52 AM on August 11

You are fairly scant on the details -- so its hard to figure out all the details -- but the answer is yes -- you could likely automate this 100% with a database such as filemaker, access or MySQL and some application logic on top of it -- or even with Excel and VBA.
posted by SirStan at 11:54 AM on August 11

Why does your report include data before 2006 in the first place?
posted by SirStan at 11:55 AM on August 11 [1 favorite]

As noted, Excel and VBA can handle just about anything. It might be worth your time to learn VBA such that you can code and maintain a solution. I personally learned it on my own, by googling for functions. But I'm a programmer by trade, so languages are easy for me.

Maybe we should modify the Ask Mefi to be: What's a good book for learning Excel Macro/VBA?

If you don't think you can do that, you're going to have to provide a lot more detail about the nature of your reconciliation.
posted by teabag at 12:02 PM on August 11

I am updating warranty expirations for servers in a lotus based program. Anything before 2006 have expired and need to be removed from the database. Yes, I know, lotus is a pain, but hey, I work for IBM.
posted by TheBones at 12:03 PM on August 11 [1 favorite]

That screams database.

UPDATE warranties SET expired = TRUE where purchase_date <>
If you have the authority to change the way you work with this data, definitely investigate a true database option, even if it's Access.
posted by odinsdream at 12:26 PM on August 11

Mefi ate my code:

where purchase_date <= '01/01/2006'
posted by odinsdream at 12:27 PM on August 11

A little different flavor here:

-Before spending too much time "learning" VBA (which will certainly include all sorts of stuff you are unlikely to use) tinker around with "Record a Macro".
-Start with a few things that are pretty simple in the recording: delete a row, select a cell and changes its contents, rename a worksheet, do something based on a conditional (sometimes it is easier when you start out to create a new column to hold your conditional results instead of doing the "right" thing and storing them as variables in the VBA)
-After you record some stuff, play around with the code and see if you can get it to do something useful (you might start this useful part by trying to "record" components of your end goal (it will take everything literally so you will still need to change everything to be programmatic/dynamic)
-Remember to do 1 (or both) of 2 things here: make the code such that it is totally additive (always making new stuff and modifying that), or design your processes such that you can run extra iterations of this code on the same "input" (which you will save and not mess with)
-You will spend a whole day debugging this and you do not want to have to create the input file again and again.
-Before you even touch a VBA book, you should run through this exercise and see how much of the work you can automate on your own (this will also focus your reading a great deal).
-Without any help, you can probably automate 50% of the bitch work (you might be happy enough to stop there, because the other 50% might be a pain in the ass)
-Also, there are TONS of tutorials out there for all sorts of tasks (use the VBA functions you get from recording in your queries for the appropriate tutorials)

This is not the purist way to do it, but it is the way that non-coders can get off the ground immediately and start doin' it to it.

Good luck!
posted by milqman at 12:39 PM on August 11 [4 favorites]

I agree with milqman. I usually use that as my code base for any Excel macro. Then, if there is something I can't figure out how to code, same thing. Just record it, then modify it, and add the snippet in the my main code.
posted by Climber at 12:48 PM on August 11

I love using an excel plugin called 'dig db' which basically provides a simple interface for creating macros. It's incredibly helpful.
posted by miss tea at 12:51 PM on August 11 [2 favorites]

Am I using the right program, or should I be looking at a local copy of a mysql database with a GUI (as I'm not that good of a programmer).

Should I use some other database, such as bento, filemaker pro or even access?


Yes, Access would make this a lot easier. Play around with importing your data into Access and writing a few select queries (using the wizard, not SQL yet).

In Excel, I use VLOOKUP as a quick a dirty version of what you can do in Access by making a simple select query that joins two tables based on a single field they have in common. Using this query, you can get only the ones that match or only the ones that don't (unmatched), or you can get everything from one table and only the ones that match in the other table. Truly useful.

I have about 10 to 20 a month and it keeps me from doing my real job.

If you got someone to write the queries and Macro for you, you could just run it as needed.
posted by soelo at 12:55 PM on August 11

I think you could write a macro to do this rather easily, without having to read any sort of book or doing a bunch of crazy research. I recently taught myself basic VBA to do a very similar task- here's my question and answers, which includes some sample code.

I basically created a loop (which you can google to learn how to do) that used a counter to go line by line and do what I wanted. I used "record macro" to find the code for what I wanted (copy, paste, etc) to do, and then edited it to be more dynamic.

For instance, when I recorded a macro, I might hit record, and then copy the contents of cell A1 to cell B1. The recorded macro would look like this:

Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

Now, a macro isn't very useful if it can't do that same thing for each row. So, you have to make the code dynamic. Here is example code that would go row by row and copy column 1 into column 2:

Dim counter As Integer *(This declares variable counter as an integer)

For counter = 1 To 1000 *(This sets up your loop to go through rows 1 through 1000)

Range(counter,1).Select *(This selects the cell on the row that the counter is on)
Selection.Copy
Range(counter,2).Select *(This selects the 2nd column on the row that the counter is on)
ActiveSheet.Paste

Next counter *(this is the end of your loop- tells it to go to the beginning of your code and add 1 to the counter variable)


So, that code would go through rows 1 to 1000 and copy anything from the first column to the second column. Obviously, you could just select the entire column 1 and copy it into column 2, but that wouldn't make for a good example, would it? The point is, you can add useful things into this code. For example:

Dim counter As Integer

For counter = 1 To 1000

If Cells(counter,1).Value = Cells(counter,3).Value Then
Range(counter,1).Select
Selection.Copy
Range(counter,2).Select
ActiveSheet.Paste
Else
Range(counter,1).Select
Selection.Copy
Range(counter,4).Select
ActiveSheet.Paste

Next counter


Now this code will check to see if the value of column 1 is equal to the value of column 3. If it is, then it copies the value of column 1 into column 2, and if not, into column 4. It does this for every row.

Using a method like this is very simple and extendable- you can work with different worksheets, add in variables and constants, etc etc. I think you could easily come up with something to do what you want.

If you would like, I could try and write up something that would actually reflect what you need to do, but I would need more details. Just mefi mail me if you're interested!

Also- for any VBA pros out there, I know, I know, there is a much easier way of copying the value instead of the whole .select .copy and .paste. I just used that example because its what the record macro output and I figured it was easier to understand. Please don't flame me.
posted by kraigory at 4:47 PM on August 11

THANK YOU ALL SO MUCH! I'm gonna look into access, as I would like to learn it anyway. I will also mess around with recording macros as well as checking out dig db.
posted by TheBones at 5:13 PM on August 11

You guys rock!
posted by TheBones at 5:13 PM on August 11

You could probably learn enough python in ~1 month to make this a lot easier. If I was motivated, I could probably automate your process in a couple of hours and a couple hundred lines of code. Stupid little tasks like this is exactly why I learned it.
- SQLite for a small database (built-in python library to manipulate)
- XLRD to manipulate xls files you are given.
- CSV (built-in python library) if xlrd isn't cutting it.

There's a ton of questions about how to learn python on StackOverflow if you're interested and a lot of great free online resources. Here's a place to start.
posted by i_am_a_Jedi at 5:47 PM on August 11

i_am_a_Jedi: You could probably learn enough python in ~1 month to make this a lot easier. If I was motivated, I could probably automate your process in a couple of hours and a couple hundred lines of code. Stupid little tasks like this is exactly why I learned it.

I was about to say the same thing - getting into python is exactly 1,000,000 times easier (+ more useful) than learning sodding Visual Basic, if you ask me.

...and you can do everything you need to with Excel in Python with the use of two external modules: i_am_a_Jedi's aforementioned xlrd, which doesn't actually manipulate xls files but simply reads them (hence the name), and xlwt, a module which (predictably) writes xls files. Both of these modules derive from the ancient and now-obsolete PyExcelerator module, which you can ignore.

If you're interested, here's an incredibly handy cheatsheet [pdf, via] which will tell you in less than a page everything you need to know to write information to Excel files via python; reading the data through xlrd is, as I recall, even easier. And he's right - in a few hundred lines, it should be possible to write something to make this a lot easier for you; but don't let that intimidate you, as most of that would be fluff to make it more user-friendly. In truth, python is so damned easy and simple that you can script any everyday task with ten lines or so; for example

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("This is the name of your sheet")
worksheet.write(2, 4, "boobies!")
workbook.save(r"C:\this_is_your_workbook_file.xls")

will make a new workbook and write the rather pleasant word "boobies!" in cell C5. (That's what the "2, 4" bit in the write command stands for - 2 stands for the "C" column, since python indexes always start with 0, and 0, 1, 2 are A, B, C, and 4 similarly stands for the fifth row.)

See how sensible that is? God, I love Python. You can do all that in five lines - imagine how much you can do in a few dozen.
posted by koeselitz at 9:10 PM on August 11