Excel Point of Sale (POS) - Programing multibuy deals in Excel VBA

[mps]

Hi,

I am writing a Point of Sale system in Excel using VBA. (It is long story as to why I am doing this and I know it is probably crazy using Excel and VBA as the basis for a point of sale system but it seems to be working!!)

I currently have an Excel worksheet which is used to hold the items in the sale:

Column A = Item Code
Column B = Quantity
Column C = Item Price
Column D = Tax Code

What happens is, when the user enters an item code in column A using VBA I look up the products details which are stored on a separate worksheet and copy across the item's price and tax code. When the user clicks a 'take payment' button on the worksheet I use a userform to inform them of sale total and for them to enter different payment methods / amounts. When the user has taken payment then a receipt is printed out and the sale's data is stored in another worksheet. The person who I am writing this system for has asked me if I could incorporate multibuy deals into the sytem, specifically:

1) Buy X DIFFERENT products from a selection of products and get them @ a set price - this would enable you handle bundle deals eg. bundle 3 items together for a set price
2) Buy X (SAME OR DIFFERENT) products from a selection of products and get them @ a set price - this would enable you do handle 'Buy get one free' kind of deals, '3 for 2' kind of deals and '2 CDs for £15' kind of deals

Working out which multibuy deals apply to the list of items the purchaser is buying has made things more difficult than just adding up item prices for the sale. Obviously I need to add a 'tag' to each product's details identifying what kind of multibuy deal (if any) it is linked to. However at this point I am struggling to know how to program these multibuy deals using Excel Worksheets and VBA. For me what seems to makes it more difficult is that for each product line in the sale the quantity of each item (Column B above) could be greater than 1.

I am not looking for anyone to write the code for me, but if anyone can help by suggesting an outline for a general algorithm to work out which multibuy deals to apply to the sale I would be very grateful!!

If I am posting this query in the wrong place I apologise and could you direct me to the best place to post it.

ALSO Posted on: http://www.ozgrid.com/forum/showthread.php?t=154740

Thanks

Martin

 

[borgem]

One option could be to add in a hidden column which references the type of sale on the item. i.e. "R" for regular, "BOGO" for buy-one-get-one, and "Bund" for bundle, or something like that. Then you could use a macro to apply an autofilter to the items, sorting first by price (high to low) and then by sale type. This way, you could have the higher priced item for BOGO listed first, so they get the "equal or lesser value" item free or half off. Next, you would want to attack how to determine if enough of the sale items have been placed in the cart. Obviously you would have to add in some extra coding to verify that the best deal is obtained if more than 2 BOGO items are chosen.

I hope this points you in the right direction.

The autofilter macro would contain code like this:

With Sheet1              
    .AutoFilterMode = False                          
    .Range("A4:E4").AutoFilter
    .Range("A4:E4").AutoFilter Field:=1, Criteria1:=.Range("A2")

 

[SMB Cooper]

How about another Column = Deals... when the user enters a "Deal Code" VBA will look for the "Deal" stored on a helper sheet and copy the "Deal" formula to the "system".

 

[mps]

Thanks for both those suggestions they help me get started in the right direction - I think the complicating factor is that I need to work out the deals twice:

1) I need to work out which deals to apply on the reciept, showing the original item prices and at the end of the receipt naming and taking off the deals.
2) Because of the computerised accounting system the company uses - the sales data from the EPOS system needs to be uploaded as an Excel file. To create the sales data I take the items from the basket and I need to adjust individual item prices to make the sale add up to the sale total (including deals) as I am told I cannot upload a deal code.

It is a bit of a headache really and I can't see anyway I can simplify it.

 

[borgem]

Is this format set in stone, or could the receipt simply list the sale and amount of savings under each applicable item?

Item 1 $Price
-BOGO -$Price
Item 2 $Price
Item 3 $Price

Where Item 3 & Item 1 might pair for the sale, but Item 3 was the higher priced item. (you would have a seperate/hidden sheet rearranging and calculating sales)

This is just an example pulled from how a lot of grocery store's display sales. They still list the items in the same order that they were scanned in, but then they summarize the subtotal, total savings and new total at the bottom of the receipt.

 

[mps]

Over the last few months I have been working on creating a macro enabled Excel Worsheet that is an EPOS (Electronic Point of Sale) system. I have now completed the first version and released it opensource. You can download it from http://epos4excel.weebly.com. See the user manual / source code for more details on how I programmed multibuy deals. Thanks to all who helped me with it!