E90Post
 


Coby Wheel
 
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
Cyprus
209
Rep
3,358
Posts

Drives: 2010 X5
Join Date: Jul 2006
Location: NYC

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!
__________________
Appreciate 0
      04-09-2007, 08:52 PM   #2
ages944
Captain
ages944's Avatar
Poland
97
Rep
990
Posts

Drives: E90 325i
Join Date: Dec 2005
Location: Chicago 'burbs

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
Appreciate 0
      04-09-2007, 09:50 PM   #3
Dleo
Brigadier General
Dleo's Avatar
Cyprus
209
Rep
3,358
Posts

Drives: 2010 X5
Join Date: Jul 2006
Location: NYC

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:
__________________
Appreciate 0
      04-09-2007, 10:01 PM   #4
MoreCowbell
Lieutenant Colonel
MoreCowbell's Avatar
485
Rep
1,700
Posts

Drives: 24 i5M60 23 X740 23 Vette Z06
Join Date: Feb 2006
Location: Westchester, NY

iTrader: (0)

It's time for Microsoft Access.
Appreciate 0
      04-09-2007, 10:48 PM   #5
ages944
Captain
ages944's Avatar
Poland
97
Rep
990
Posts

Drives: E90 325i
Join Date: Dec 2005
Location: Chicago 'burbs

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
Appreciate 0
      04-10-2007, 12:22 AM   #6
markis64
Lieutenant
United_States
60
Rep
419
Posts

Drives: 2006 E90 Alpine White 330xi
Join Date: Dec 2006
Location: Long Island NY

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
Appreciate 0
      04-10-2007, 07:27 AM   #7
FrankoQ
Deny everything
FrankoQ's Avatar
United_States
217
Rep
11,378
Posts

Drives: 2006 330i; 2015 X6, S1000RR
Join Date: Apr 2006
Location: Beautiful MD

iTrader: (5)

Garage List
2015 BMW X6  [0.00]
2006 330i  [0.00]
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

Appreciate 0
      04-10-2007, 08:53 AM   #8
Scottz71
Colonel
Scottz71's Avatar
United_States
60
Rep
2,577
Posts

Drives: '16 F25 X3 - Space Grey
Join Date: Jan 2007
Location: Redondo Beach, CA

iTrader: (0)

Garage List
Have you thought about sorting and formatting the information you need to append each month and using the VLOOKUP function in excel?
__________________
'16 F25 X3 3.5XLine - Space Gray (Riley)
'15 981 Cayman GTS - Pure White (The Wolf)
'07 E90 335i - Titanium Silver (Kreiger)
'07 E92 335i - Space Gray (Brock) retired
'15 i3 REx Giga World - Capparis White (Randy) retired
Appreciate 0
      04-10-2007, 12:21 PM   #9
gmcwv
Private First Class
5
Rep
112
Posts

Drives: '06 330i Sport
Join Date: Oct 2005
Location: Northern WV

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
Appreciate 0
      04-10-2007, 06:09 PM   #10
BMW2006
Second Lieutenant
United_States
12
Rep
217
Posts

Drives: 2007 335i Montego Coupe
Join Date: Aug 2006
Location: Columbus, ohio

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
Appreciate 0
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



All times are GMT -5. The time now is 09:25 PM.




e90post
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
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