Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 28th 05, 07:25 PM
malik641
 
Posts: n/a
Default


Quick question: Are you using the retail markup method? Or just your
standard (Price*Percent)+Price???

Just want to know before I get started on question 2 that you have

As for the rest....I'm leaving work now, but I'll read it later after
class and try to help whether or not anybody responds


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=391062


  #2   Report Post  
Old July 28th 05, 07:58 PM
ElNino
 
Posts: n/a
Default Need help making a spreadsheet for my business.

Hello,

I am new to Excel. I am starting a business online, and I am using osCommerce to run my online shop. I have installed a contribution for osCommerce called "Easy Populate", and I am in need of help. Easy populate exports a tab-delimited text file with the info in my store's database regarding my products, and my vendor provides me with a daily ..csv file with all of the data relating to their products. Once I have these two files, I am supposed to merge them both into a single tab-delimited text file (using Easy Populate's structure) to import into my store's database.

I have 2 questions that I need help with:

1) How do I merge these 2 files into a single file? The rules of the resulting file should be as follows... The products whose model number already exists in the Easy Populate file should only have their prices updated, and the products whose model number does not exist in the Easy Populate file should be added either at the top or at the bottom of the resulting file because they will need to be manually edited to include the missing info that is not provided by the vendor's .csv file, and will need to be easily identified so that the import will not give errors due to missing data.

2) The resulting file should have a formula applied to it so that the prices from the vendor's .csv file are adjusted according to the following table:

Price ----------------- Mark Up
$1-$50 ---------------- 30%
$51-$100 -------------- 20%
$101-$200 ------------- 15%
$201+ ------------------ 10%

I am totally clueless on even where to start on this, and am in need of dire help! Someone please give me some pointers on how to do it.

--
ElNino
  #3   Report Post  
Old July 29th 05, 09:17 AM
ElNino
 
Posts: n/a
Default

Hello Malik,

Thanks for looking into this for me! I can't even begin to tell you how much
it is appreciated. As to your question... I think I would prefer the retail
markup method, as it would make things easier on accounting. Thanks again! I
can't wait to see your response!

--
ElNino


Quick question: Are you using the retail markup method? Or just your
standard (Price*Percent)+Price???

Just want to know before I get started on question 2 that you have



  #4   Report Post  
Old July 29th 05, 12:43 PM
malik641
 
Posts: n/a
Default


Just to double check myself....(I haven't done this in a while)...

Retail mark-up method:

$500 with a markup of 20%
=(500*0.20)+500
=$600

And Retail margin:

$500 with a 30% margin
=500/0.70
=714.29


And you want the retail *markup* method, right?


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=391062

  #5   Report Post  
Old July 29th 05, 02:36 PM
malik641
 
Posts: n/a
Default


I'm not too sure yet how you will combine the two files into one
yet...I'm still kinda new to Excel's capabilities and how to use them

Although I'm pretty sure you're going to need a macro to do that, which
I'm trying to figure out right now. But for now, I have something for
you to play with. Check out the attachment. I gave you two ways to do
the markup (either a formula or a macro).

I think you will want to use the macro function procedure because when
you combine the two files into one and there is a new model number, you
can use the macro that will combine the two functions to add the formula
in the adjacent cell to display the markup value INSTEAD of manually
typing in the formula. I'm sure you would rather it be done
automatically.

Tell me what you think of the attached file (worksheet and VB Editor).

Hope this is a good start!


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3656 |
+-------------------------------------------------------------------+

--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=391062



  #6   Report Post  
Old July 29th 05, 05:57 PM
ElNino
 
Posts: n/a
Default


I have registered on these forums now. For some reason the messages I
just posted on the newsgroup haven't made it here yet. I suppose it's
not instantly synchronized with the newsgroup or something, but I still
think that this is pretty cool. I like these types of boards better than
newsgroups anyways.


--
ElNino
------------------------------------------------------------------------
ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725
View this thread: http://www.excelforum.com/showthread...hreadid=391062

  #7   Report Post  
Old July 29th 05, 06:25 PM
ElNino
 
Posts: n/a
Default

Ahh, I see the difference now. I'm going to need the margin method actually.
I'm probably going to need to rethink my chart now that I have been
enlightened with this info, but I can probably adjust that easily if need be
once I see the actual excel formula to accomplish this.
--
ElNino


  #8   Report Post  
Old July 29th 05, 06:27 PM
malik641
 
Posts: n/a
Default


ElNino Wrote:
so I'm going to need a
while to learn...

Meeeee toooo....I bought 2 books on excel (formulas and VBA) because
I've been working with it SOO much! And yes, excel is EXTREMELY
powerful.


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=391062

  #9   Report Post  
Old July 29th 05, 06:40 PM
ElNino
 
Posts: n/a
Default

Sweet! I didn't even know that excel had a built-in VB editor until now.
That is just awesome!! It's been a while since I used VB though, and I've
never used Excel/macros for that matter until now, so I'm going to need a
while to learn, but this looks very promising. You have opened my eyes to
the power of Excel! This program just became 1000 times more useful in my
eyes. =)

--
ElNino


I think you will want to use the macro function procedure because when
you combine the two files into one and there is a new model number, you
can use the macro that will combine the two functions to add the formula
in the adjacent cell to display the markup value INSTEAD of manually
typing in the formula. I'm sure you would rather it be done
automatically.

Tell me what you think of the attached file (worksheet and VB Editor).



  #10   Report Post  
Old July 30th 05, 12:11 PM
ElNino
 
Posts: n/a
Default


OK, I have thunk up a new chart based on the retail margin method and
here it is:

$1-50 - 25% (Cost/.75)
$51-75 - 20% (Cost/.8)
$76-100 - 15% (Cost/.85)
$101+ - 10% (Cost/.9)

Now I just need to figure out the formula/macro to make it work.


--
ElNino
------------------------------------------------------------------------
ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725
View this thread: http://www.excelforum.com/showthread...hreadid=391062



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
making changes to excel file on Windows 2003 small business server Michael Flowers Excel Discussion (Misc queries) 6 July 5th 05 02:55 PM
Spreadsheet Dilemma msbates2004 Excel Discussion (Misc queries) 1 May 27th 05 02:55 AM
Scan and copy cells from one spreadsheet to another. Mark Excel Discussion (Misc queries) 1 April 6th 05 06:45 PM
Some exported records do not show on spreadsheet vulcan88 Excel Worksheet Functions 0 March 30th 05 01:11 AM
Applying Existing Password to New Spreadsheet Vic Excel Discussion (Misc queries) 1 January 27th 05 12:37 AM


All times are GMT +1. The time now is 05:50 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017