Need help making a spreadsheet for my business.

[malik641]

Quick question: Are you using the retail markup method? Or just you
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 afte
class and try to help whether or not anybody respond

 

[ElNino]

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]

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!

 

[malik641]

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]

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

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

I think you will want to use the macro function procedure because whe
you combine the two files into one and there is a new model number, yo
can use the macro that will combine the two functions to add the formul
in the adjacent cell to display the markup value INSTEAD of manuall
typing in the formula. I'm sure you would rather it be don
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
+-------------------------------------------------------------------

 

[ElNino]

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

 

[ElNino]

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.

 

[malik641]

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.

 

[ElNino]

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]

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]

So, have ya given up on me? I still need help on this.... anyone???

 

[malik641]

Sorry ElNino...haven't checked this forum in a day or 2

Here ya go!


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

 

[David McRitchie]

For your information, and I should have noticed it was going to be a zip file

Invalid Attachment specified. I

 

[malik641]

huh......
I thought zip files were valid file extensions????

maybe I'll try this again. If it doesn't work I'm sorry and you can P
me with your e-mail address and I'll send it to you

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

 

[David McRitchie]

The newsgroups are strictly text, so have no idea what Excel Forum
actually does, as long as attachments stay at Excel Forum they
don't cause a problem with newsgroups. Didn't you get an error
when you clicked on it.

 

[malik641]

No, no error...

I'm not joined to any news groups either.....so I guess you have to be
a registered user to view it????

Where would I find an Excel newsgroup??

 

[David McRitchie]

You are posting from ExcelForum so I presume you joined that.

If you are not behind a firewall, the best way to view the newsgroup
is via Outlook Express, almost a necessity if you are on dial-up.
If you use Outlook Express for your email, then you can use it
for your newsgroups as well.

http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/ie/oe6.htm

 

[ElNino]

I was able to download both files just fine.

Thanks for your help malik! The info on VB and the macros were ver
helpful, but I really don't know how to apply it to what I'm trying t
accomplish. Is there someone out there that could possibly give me
step-by-step process on how to do this

 

[David McRitchie]

To install the macros once unzipped see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This thread illustrates one of the problems of not using a newsreader
to read and post messages, you replied to Malik, but your
message was threaded as if a reply to mine because mine was
the last reply in the thread. See the threaded view in Google,
and it is a threaded view of postings that people using a newsreader
would see, if not hiding postings they had read, and in any case
their replies would be threaded directly under (indented) to the post they
replied to..
http://google.com/groups?threadm=ElNino.1t546q_1122991629.933@excelforum-nospam.com

Note Google Groups is set apart from Google Web search with many
of the reposting websites, a web search is forced to have go through
both in fact probably several instances of postings in newsgroups.

You get new stuff, hints of what to look for in newsgroups, but you
normally get (used to anyway) a more organized structure of

 

[ElNino]

Thanks David, that info was pretty helpful to me in understanding macro
and their usage, but what I am looking for is a more personalized guid
on how I can take the 2 spreadsheets I have (one from my vendor, an
another from my site) and insert certain data from the vendor'
spreadsheet into the one from my site, then run the macro to update th
prices