E90Post
 


 
BMW 3-Series (E90 E92) Forum > BIMMERPOST Universal Forums > Off-Topic Discussions Board > Need help with EXCEL...



Reply
 
Thread Tools Search this Thread
      04-09-2007, 07:14 PM   #1
Dleo
Brigadier General
 
Dleo's Avatar
 
Drives: 2010 X5
Join Date: Jul 2006
Location: NYC

Posts: 3,357
iTrader: (2)

Need help with EXCEL...

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!
__________________
Dleo is offline   Cyprus
0
Reply With Quote
      04-09-2007, 08:52 PM   #2
ages944
Captain
 
ages944's Avatar
 
Drives: E90 325i
Join Date: Dec 2005
Location: Chicago 'burbs

Posts: 990
iTrader: (0)

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.
__________________
BMW : Sine qua non

Mods: Blacklines | Rear Seat Power Outlets | Rear Fog Lights | Ashtray Garage Door Opener
ages944 is offline   Poland
0
Reply With Quote
      04-09-2007, 09:50 PM   #3
Dleo
Brigadier General
 
Dleo's Avatar
 
Drives: 2010 X5
Join Date: Jul 2006
Location: NYC

Posts: 3,357
iTrader: (2)

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:
__________________
Dleo is offline   Cyprus
0
Reply With Quote
      04-09-2007, 10:01 PM   #4
MoreCowbell
Lieutenant Colonel
 
MoreCowbell's Avatar
 
Drives: 2007 e92 335 Jet Black-Saddle
Join Date: Feb 2006
Location: NYC

Posts: 1,639
iTrader: (0)

It's time for Microsoft Access.
__________________
1997 Z3 3.0: Retired 2006 E90 330i: Retired 2007 E92 335: Retired
2009 E70 4.8: Totaled by Drunk Bitch
2011 E70 3.5D: Current
Space Gray w Black Nevada - Loaded!!!
MoreCowbell is offline   United_States
0
Reply With Quote
      04-09-2007, 10:48 PM   #5
ages944
Captain
 
ages944's Avatar
 
Drives: E90 325i
Join Date: Dec 2005
Location: Chicago 'burbs

Posts: 990
iTrader: (0)

lol, that was my first thought
__________________
BMW : Sine qua non

Mods: Blacklines | Rear Seat Power Outlets | Rear Fog Lights | Ashtray Garage Door Opener
ages944 is offline   Poland
0
Reply With Quote
      04-10-2007, 12:22 AM   #6
markis64
Lieutenant
 
Drives: 2006 E90 Alpine White 330xi
Join Date: Dec 2006
Location: Long Island NY

Posts: 419
iTrader: (1)

Garage List
3006 e90 330xi  [0.00]
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
markis64 is offline   United_States
0
Reply With Quote
      04-10-2007, 07:27 AM   #7
FrankoQ
Deny everything
 
FrankoQ's Avatar
 
Drives: 2006 330i
Join Date: Apr 2006
Location: Laurel, MD

Posts: 11,297
iTrader: (4)

Garage List
2006 330i  [0.00]
Send a message via AIM to FrankoQ
Quote:
Originally Posted by markis64 View Post
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.
__________________

I need a drink

FrankoQ is offline   United_States
0
Reply With Quote
      04-10-2007, 08:53 AM   #8
Scottz71
Colonel
 
Scottz71's Avatar
 
Drives: e92 335i
Join Date: Jan 2007
Location: NoVA

Posts: 2,570
iTrader: (0)

Garage List
2007 335i (e92)  [1.00]
Send a message via AIM to Scottz71
Have you thought about sorting and formatting the information you need to append each month and using the VLOOKUP function in excel?
__________________
Scottz71 is offline   United_States
0
Reply With Quote
      04-10-2007, 12:21 PM   #9
gmcwv
Private First Class
 
Drives: '06 330i Sport
Join Date: Oct 2005
Location: Northern WV

Posts: 112
iTrader: (0)

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
gmcwv is offline  
0
Reply With Quote
      04-10-2007, 06:09 PM   #10
BMW2006
Second Lieutenant
 
Drives: 2007 335i Montego Coupe
Join Date: Aug 2006
Location: Columbus, ohio

Posts: 217
iTrader: (0)

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
BMW2006 is offline   United_States
0
Reply With Quote
Reply

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -5. The time now is 08:40 AM.




e90post
Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
1Addicts.com, BIMMERPOST.com, E90Post.com, F30Post.com, M3Post.com, ZPost.com, 5Post.com, 6Post.com, 7Post.com, XBimmers.com logo and trademark are properties of BIMMERPOST