Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#11
|
|||
|
|||
So, have ya given up on me? I still need help on this.... anyone??? -- ElNino ------------------------------------------------------------------------ ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#12
|
|||
|
|||
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 | +-------------------------------------------------------------------+ -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#13
|
|||
|
|||
For your information, and I should have noticed it was going to be a zip file
Invalid Attachment specified. I "malik641" wrote Sorry ElNino...haven't checked this forum in a day or 2 |
#14
|
|||
|
|||
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 PM 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 | +-------------------------------------------------------------------+ -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#15
|
|||
|
|||
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" wrote in message ... 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 PM 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 | +-------------------------------------------------------------------+ -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#16
|
|||
|
|||
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?? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#17
|
|||
|
|||
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "malik641" wrote in message ... 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?? -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#18
|
|||
|
|||
I was able to download both files just fine. Thanks for your help malik! The info on VB and the macros were very helpful, but I really don't know how to apply it to what I'm trying to accomplish. Is there someone out there that could possibly give me a step-by-step process on how to do this? -- ElNino ------------------------------------------------------------------------ ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#19
|
|||
|
|||
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=ElN...rum-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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "ElNino" wrote in message ... I was able to download both files just fine. Thanks for your help malik! The info on VB and the macros were very helpful, but I really don't know how to apply it to what I'm trying to accomplish. Is there someone out there that could possibly give me a step-by-step process on how to do this? -- ElNino ------------------------------------------------------------------------ ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#20
|
|||
|
|||
Thanks David, that info was pretty helpful to me in understanding macros and their usage, but what I am looking for is a more personalized guide on how I can take the 2 spreadsheets I have (one from my vendor, and another from my site) and insert certain data from the vendor's spreadsheet into the one from my site, then run the macro to update the prices. -- ElNino ------------------------------------------------------------------------ ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help making a spreadsheet for my business.
UPDATE: I just thought I'd drop in and let you guys know that I figured out the hardest part of this project today - FINALLY! :) :) :) :) My brain must have gone into overtime today, lol because It just dawned on me all of a sudden (strange how these things happen, eh?). Anyways, I'll explain how I was able to do this with 2 simple excel formulas. First of all, in my vendor's spreadsheet, they give the stock quantities of 12 of their locations seperately, so I needed to figure out a formula to add them all up. They also have a "status" column that states if the product is active or discontinuing, so I needed to make sure the quantity of the products whos status is "discontinuing" was 0. Also, in some of the products, instead of giving an exact stock quantity, they put the word "Available" in the cell that is supposed to contain the quantity. I don't know why they did this, but since I had no idea how many were actualy available, I chose to replace the word "Available" with 99 in the same formula. The status column was column K and the quantity columns were L-W. To make this work, first I selected empty cell X2, and made the following formula: Code: -------------------- =SUM(L2:W2)*IF(K2="DISCONTINUING",0,1)+IF(L2="Avai lable",99,0)+IF(M2="Available",99,0)+IF(N2="Availa ble",99,0)+IF(O2="Available",99,0)+IF(P2="Availabl e",99,0)+IF(Q2="Available",99,0)+IF(R2="Available" ,99,0)+IF(S2="Available",99,0)+IF(T2="Available",9 9,0)+IF(U2="Available",99,0)+IF(V2="Available",99, 0)+IF(W2="Available",99,0) -------------------- Then after applying this formula to cell X2, I highlighted it, and the rest of the cells below it and edit- fill - down to fill the rest of the cells in that column with data. The only problem I had with this formula is when I put *IF(K2="DISCONTINUING",0,1) at the very end like I wanted it instead of right after the SUM statement, it didn't calculate right. I dunno why this happened, but it did. So I am just going to have to deal with the very few products whos status is discontinuing, and their quantity is "Available" getting a number other than 0 for now (no biggie). If anyone could tell me why this is happening, I would really love to know. For the next formula, I needed to get my vendor's price from column F and apply that price to my markup chart. For simplicity's sake, I decided not to use the retail markup method. I made up a new chart once again,and this is the one I am going to stick with: <$10 ---------------- Add $3 $10-$49.99 ---------- *1.3 (30%) $50-$74.99 ---------- *1.2 (20%) $75-$99.99 ---------- *1.15 (15%) $100+ --------------- *1.1 (10%) Once the markup is applied, I needed to output the result to column Y. Here is the magical formula that made this work, which has forever eluded me until today: *drumroll* Code: -------------------- =IF(F2<10,F2+3,IF(AND(F2=10,F2<50),F2*1.3,IF(AND( F2=50,F2<75),F2*1.2,IF(AND(F2=75,F2<100),F2*1.15 ,F2*1.1)))) -------------------- Once this code was applied to Y2, I used the same method of filling the rest of the column as earlier. Only took me 6 months to figure out, lol. But I am proud of myself considering that I had already given up and was going to hire someone to do this for me. Now,I just need to get figure out a way to have these formulas automatically applied to my daily vendor spreadsheet, delete the unnecessary rows, and merge with the easy populate spreadsheet from my website so it can be uploaded. I don't think that will be nearly as much of a brain teaser as these damn formulas were for me though. -- ElNino ------------------------------------------------------------------------ ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#23
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help making a spreadsheet for my business.
Don Guillett Wrote: a cursory look suggests a bit shorter countif(l2:w2,"available")*99 -- Don Guillett SalesAid Software Hey, thanks Don... it worked! I even figured out why it wouldn't add up correctly if I placed *IF(K2="discontinuing",0,1) at the end of this formula (I had to seperate the equations). So, this is my new formula to add up the quantitty: Code: -------------------- =(SUM(L2:W2)+COUNTIF(L2:W2,"Available")*99)*IF(K2= "discontinuing",0,1) -------------------- :) -- ElNino ------------------------------------------------------------------------ ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 |
#24
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help making a spreadsheet for my business.
glad to help
-- Don Guillett SalesAid Software "ElNino" wrote in message ... Don Guillett Wrote: a cursory look suggests a bit shorter countif(l2:w2,"available")*99 -- Don Guillett SalesAid Software Hey, thanks Don... it worked! I even figured out why it wouldn't add up correctly if I placed *IF(K2="discontinuing",0,1) at the end of this formula (I had to seperate the equations). So, this is my new formula to add up the quantitty: Code: -------------------- =(SUM(L2:W2)+COUNTIF(L2:W2,"Available")*99)*IF(K2= "discontinuing",0,1) -------------------- :) -- 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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making changes to excel file on Windows 2003 small business server | Excel Discussion (Misc queries) | |||
Spreadsheet Dilemma | Excel Discussion (Misc queries) | |||
Scan and copy cells from one spreadsheet to another. | Excel Discussion (Misc queries) | |||
Some exported records do not show on spreadsheet | Excel Worksheet Functions | |||
Applying Existing Password to New Spreadsheet | Excel Discussion (Misc queries) |