E90Post
 


Lux Angel Eyes
 
BMW 3-Series (E90 E92) Forum > BIMMERPOST Universal Forums > Off-Topic Discussions Board > Calling All Computer Nerds.....



Reply
 
Thread Tools Search this Thread
      01-03-2008, 02:32 PM   #1
Dleo
Brigadier General
 
Dleo's Avatar
 
Drives: 2010 X5
Join Date: Jul 2006
Location: NYC

Posts: 3,357
iTrader: (2)

Calling All Computer Nerds.....

Mission:

Save me hours and hours of data entry, you know, copy and paste, copy and paste,....... copy and paste.

Brief:

Its basically a very big excel workbook right now, it has current customers for each different brand our company has *Note not all customers carry every brand.
For each customer there is 12 empty boxes next to the name (months of the year)
Every month we get a monthly depletion (also another excel worksheet), saying who bought what and how many.
The problem is, sometimes theres more then 1,000 orders that need to be inputed and also tons of new accounts that need to be added for the specific brand.

Reward:

If you except to choose this mission and help me automate this process somehow, i will ship you a bottle of either vodka, rum, whiskey. (Depending on your preference of course)


Anyone??


If anyone is truly interested and capable of helping me automate this process I will send you the files I am working with to help you understand exactly how its setup.
__________________
Dleo is offline   Cyprus
0
Reply With Quote
      01-03-2008, 03:55 PM   #2
jumpingjz
Major General
 
jumpingjz's Avatar
 
Drives: 2007 328i Coupe
Join Date: Feb 2007
Location: Monterey Park, CA

Posts: 5,426
iTrader: (4)

if it's in excel what you can do is

1. create a search key custname_brand_month
2. create the same search key at sales spreadsheet
3. use sumif fomula to sum up sales.

hope that helps.

JZ
jumpingjz is offline   United_States
0
Reply With Quote
      01-03-2008, 04:18 PM   #3
Dleo
Brigadier General
 
Dleo's Avatar
 
Drives: 2010 X5
Join Date: Jul 2006
Location: NYC

Posts: 3,357
iTrader: (2)

Quote:
Originally Posted by jumpingjz View Post
if it's in excel what you can do is

1. create a search key custname_brand_month
2. create the same search key at sales spreadsheet
3. use sumif fomula to sum up sales.

hope that helps.

JZ
Thanks for the input, i will try messing with it, the problem is my experience with excel is limited to entering, formating, and organizing data
__________________
Dleo is offline   Cyprus
0
Reply With Quote
      01-03-2008, 04:27 PM   #4
TurboFan
Ski bum
 
TurboFan's Avatar
 
Drives: sideways
Join Date: Aug 2007
Location: Knee deep in the pow

Posts: 6,207
iTrader: (8)

Send it to me. PM me for my address. I'll either figure it out in 20 minutes or say no f'ing way. Anything up to a macro is pretty easy. If I need to write a macro....well I'm a little rusty!
__________________

1999 e46 328i Ti Silver / Black[retired]
2007 e90 335xi Jet Black / Black[retired]
2011 e70 X5 35d Vermillion Red / Cinnamon
2011 e92 M3 LeMans / Fox Red extended
TurboFan is offline  
0
Reply With Quote
      01-03-2008, 04:28 PM   #5
MCS
e90 newbie
 
Drives: 2006 330i
Join Date: Jul 2005
Location: Northern NJ

Posts: 440
iTrader: (2)

Garage List
2006 330i  [0.00]
use a sumif or a vlookup on the cusomer name to get the orders.

An Excel for dummies book (MS help sucks) can explain the vlookup funciton.
__________________
E90 TiAg 330i ZSP, ZPP, ZCW 6sd Manual, Nav & Satellite (for Stern!), Black leather w/ Poplar
MCS is offline  
0
Reply With Quote
      01-03-2008, 04:34 PM   #6
Hawkeye
Brigadier General
 
Hawkeye's Avatar
 
Drives: '07 Z4 si Coupe, '08 R6
Join Date: Aug 2007
Location: Holland

Posts: 3,594
iTrader: (0)

Garage List
I might be able to help, when do you have to have this done by? I write programs in VBA for my job all the time.

Sounds like it should be pretty easy to do, maybe 15 minutes. It would be easy to write a code to search for a company and if it exists make sure it has that brand, if not add it. If the brand doesn't exist you could tack it on the end. If you wanted to send me some examples (smaller files but set up the same please) I could probably hack something together and tell you how to finess it.

I'll pm you my info.
__________________
2007 Z4 3.0si Coupe 6 MT Black Saphire Metallic PP SP Heated M Seats Clutch Stop Stubby
2008 R6 Raven Black GPR Steering Stabilizer Comp Werks FE Hotbodies Flush Mount Blinkers
2008 Nissan Titan XE King Cab Long Bed 4x4 Line-X
Hawkeye is offline   No_Country
0
Reply With Quote
      01-03-2008, 04:39 PM   #7
Hawkeye
Brigadier General
 
Hawkeye's Avatar
 
Drives: '07 Z4 si Coupe, '08 R6
Join Date: Aug 2007
Location: Holland

Posts: 3,594
iTrader: (0)

Garage List
Quote:
Originally Posted by MCS View Post
use a sumif or a vlookup on the cusomer name to get the orders.

An Excel for dummies book (MS help sucks) can explain the vlookup funciton.
sumif and vlookup will not help adding new companies. sumif would be good if you were just looking for stuff you had to match. (customer on the side and brand along the top) and only if you didn't have a starting quantity you had to worry about. Otherwise you would have to make a presentation tab and a prior month tab or something similar.
__________________
2007 Z4 3.0si Coupe 6 MT Black Saphire Metallic PP SP Heated M Seats Clutch Stop Stubby
2008 R6 Raven Black GPR Steering Stabilizer Comp Werks FE Hotbodies Flush Mount Blinkers
2008 Nissan Titan XE King Cab Long Bed 4x4 Line-X
Hawkeye is offline   No_Country
0
Reply With Quote
      01-03-2008, 04:45 PM   #8
O-cha
Brigadier General
 
O-cha's Avatar
 
Drives: Mcoupe
Join Date: Oct 2007
Location: In front of you

Posts: 4,729
iTrader: (2)

Send a message via AIM to O-cha
Does everything follow the same characteristics? Like the monthly depletion order and new customer additions? If so it's a very easy job for a programmer to write a program that will take your order depletions and input them into the other excel sheet. Same with the new customers as long as it follows the same pattern with each entry and each month.


Thing is, programmers aren't cheap even if it's a super easy job like this one, but the bonus is it would nearly fully automate the job you just described. Upload the two spreadsheets, or an example of them if it's confidential.
__________________
O-cha is offline  
0
Reply With Quote
      01-03-2008, 04:45 PM   #9
ren
Captain
 
Drives: E92 335
Join Date: Jan 2007
Location: U.S.

Posts: 825
iTrader: (8)

I can give it a shot if you still haven't figure it out. I'm not bad with Excel modeling. If you want send me a PM I'll send you my e-mail address. Good luck!
ren is offline  
0
Reply With Quote
      01-03-2008, 05:25 PM   #10
Hawkeye
Brigadier General
 
Hawkeye's Avatar
 
Drives: '07 Z4 si Coupe, '08 R6
Join Date: Aug 2007
Location: Holland

Posts: 3,594
iTrader: (0)

Garage List
Quote:
Originally Posted by O-cha View Post
Does everything follow the same characteristics? Like the monthly depletion order and new customer additions? If so it's a very easy job for a programmer to write a program that will take your order depletions and input them into the other excel sheet. Same with the new customers as long as it follows the same pattern with each entry and each month.


Thing is, programmers aren't cheap even if it's a super easy job like this one, but the bonus is it would nearly fully automate the job you just described. Upload the two spreadsheets, or an example of them if it's confidential.
Yeah, what he said. I am really expensive....when at work. But I'd do it for a bottle of booze (just don't tell my company that!)
__________________
2007 Z4 3.0si Coupe 6 MT Black Saphire Metallic PP SP Heated M Seats Clutch Stop Stubby
2008 R6 Raven Black GPR Steering Stabilizer Comp Werks FE Hotbodies Flush Mount Blinkers
2008 Nissan Titan XE King Cab Long Bed 4x4 Line-X
Hawkeye is offline   No_Country
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 06:55 PM.




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