[Dleo]

Hey guys, I figured I would ask here and see if someone can help me.

Heres a little bit of what I need help with.

I have an excel database that has store names, address, and what brands they purchase from us. It gets into detail every month in a diffrent worksheet as to how many exactly did they buy, 1 or 2 cases etc..

At the end of the month I get another excel worksheet from our distrubutor saying who bought what and how many. This info needs to be entered into my excel database. What I usally do is just copy and paste, copy and paste. Its very time consuming considering there might be as many as 900+ sales in a month. There HAS to be a simple way to get this done, the problem is sometimes there are new accounts in the list that I have to make a new entry into my database.

I know it seems complicated but its really not, if someone thinks they might be able to help me out, PM me, I have AIM and MSN where I can explain a little better.

Thanks in advance guys!

[ages944]

First, you have an excel spreadsheet or a database? If it's a spreadsheet, have you tried copy/pasting an entire worksheet at once? Not totally getting how your files are set up though.

[Dleo]

yeah i guess its kind of hard to explain unless you really take a look at the two format of the files. no biggie tho, i just get fed up flipping screens back and forth and bought another monitor. now with a dual screen setup its at least a little easier, but i still have to copy paste, copy paste :mad:

[MoreCowbell]

It's time for Microsoft Access.

[ages944]

lol, that was my first thought

[markis64]

yeah access is cool if you know how to use it, it can get very difficult when you have a lot of tables, reports, quires etc...


i just finished using it in my computer and info tech class and it was by no means fun to learn but probably useful later on down the road.

for the excel situation maybe you could make a table withen your spreadsheet and hold certain things constant so you wont have to copy and paste so much? if im hearing you right? when you type in a formula like a vlookup formula it will just place the item from the table. Its probably a good idea to get a book like excel for dummies it has all the formulas and directions youll ever need, because its way to hard to ask people how to when it comes to that kind of stuff sometimes

[FrankoQ]

Originally Posted by markis64

yeah access is cool if you know how to use it, it can get very difficult when you have a lot of tables, reports, quires etc...


i just finished using it in my computer and info tech class and it was by no means fun to learn but probably useful later on down the road.

for the excel situation maybe you could make a table withen your spreadsheet and hold certain things constant so you wont have to copy and paste so much? if im hearing you right? when you type in a formula like a vlookup formula it will just place the item from the table. Its probably a good idea to get a book like excel for dummies it has all the formulas and directions youll ever need, because its way to hard to ask people how to when it comes to that kind of stuff sometimes

I see 2 options:
1) switch to MS Access.
2) use macros in Excel.

Either way you have a learning curve but it will beat copy/paste on the long run.

[Scottz71]

Have you thought about sorting and formatting the information you need to append each month and using the VLOOKUP function in excel?

[gmcwv]

Your best bet is to move to a database. MS Access is good if you are really only good at Windows type software.
There are a few free/share ware database applications, mySQL and Postgres are two off the top of my head. Oracle even offers there database software free...for testing and development only though... so read into that as much as you want. As long as you don't try to market it (or if your not a big company) you can get away with it. You might want to even think about MS SQL. It is really easy to use and has a lot of function to it. You just have to think about if it is more than what you really need to pay for. However though any database that you do use you can always import the information back into Excel. Too you can create sql scripts to import your data in (add vendors ect ect) and once you have the scripts created...you'll never have to copy and paste again.

If you have questions I'd be happy to help and point you in a better direction, I am a DBA so I know a few things :P

[BMW2006]

You could do it with some macro work. ALthough I'd agree with the other's sentiments about graduating to a SQL database. My recommendation would depend on what you want to do with data you are gathering. If you're mainly record-keeping I would say just write up some clever macros to save yourself thousands of keystrokes. If you are in the position of, or think it would be beneficial to anyone in the company to be able to run queries against the data, then establishing a SQL database now would be the ideal way to go.

I'd also look at how clean the data is. Are you modifying anything between the Distributors report and your own database? Are you changing an old store name to a new one, does the report list "Moutain River north 3" and "Mountain river" seperately even though they are the same store? Anything you need to intervene in between the reports I would look at very closely, as this stuff will pollute a macro'ed entered database very quickly.

Does this information exist elsewhere in the company? Do they use it? How often to you need to refer to it? No need to go through the trouble of creating a DB if the company already has and is using the information elsewhere.

If you create a database and show your manager, you may even get a raise. You'll look like a damned saint if you showed them that in the process of saving yourself 1000 copy/pastes a month, they now have the ability to run as many custom queries as they like on historical sales data (i.e show me every store that grossed $500,000 a week that also has ordered under 10 cases). Managers would eat that up like it's thier last meal.

You can always chart off of a returned query also. Again it just depends really on what the data is used for. If it's just more or less record keeping I would use macros, If you're looking for something more dynamic, SQL database all the way.

I'd also second gmcwv's comments, before you buy any database software, take a long look at what you really need it for, DB software doesn't come cheap. I'n not a big fan of Access but it's part of the office suite, which you probably already have.

Let us know what you decide to do!

-BMW2006