Complex Product Selection Tool. Any ideas?

[zappabeef]

Hi all,

I have been set a challenge by my boss to create a "simple yet sophisticated" product selection tool for our customers to use in order to make an order and give them a unique part code.
Basically our customers ask for a combination of products and we send it to them in a box.
Each box has a unique "Part code" so that the customer can enter the code for repeat purchases.

Please see attached a spreadsheet that will hopefully help you understand what I need.

Question 1. In the combinations tab Can I generate this combination list using formula/code?
Question 2. In the product selection tab Can I create an easy interface for customers to easily select the combination they need as like


If you could explain any formula or code so that I can reuse and reapply it I would be very grateful.

Many thanks!

Matt

[ChemistB]

Question 1: Are we talking about column N? How do you develop that number?
Question 2: I assume the Kimal Part Number and Bag Part Number would automatically follow once the dialyser and bag were chosen? Ultra Steri Set and Sanicloth can just be yes/no? Would you prefer they choose Needle or Needle Description (the other would automatically fill)? Anything else I should know?

[zappabeef]

Hi Chemist thank you for your speedy response, very impressive!

Firstly for clarification please rename column E title to Dialyser Part number and G to Bag part number.

Question1: Yes column N. This code is generated in the following format Prefix [KPSM] , [C/F] depending on column b (fistula or catheter), [D or F] also depending on column b HD or HDF respectively, then 12345 sequential number. As these part numbers are already being used by the customer i will leave them be but it would be useful to see how this would be generated for future product ranges.

Question2: You're quite right dialsyer and bag part numbers should automatically follow and are consistent throughout. Steri set and sani cloth are just a yes no.....would be neat if we could have a yes/no option and the part number would automatically follow. GAM-103564 for steri and CA2C200 for sani.

Other thing to mention is that at some point we would like to include a field for costs. i hope this doesnt complicate things and just creating a matrix is our first step but cost generation would be extremely useful.

many thanks ChemistB

[zappabeef]

My objectives are to

1. Develop a list of all possible product combinations for future product ranges, like the one in the combinations tab and assign each combination one with a unique and sequential product code.
(I did this the long way round and would be interested to see if there is a an excel function that would speed the process of creating this list up, it took me ages!)

2. develop a simple customer friendly product selection interface roughly like the one in the selection tab
3. Include price as a variable. whilst this isn't essential now it would be useful to understand how this might be incorporated at a later date.

Your help is much appreciated

regards,

Matt

[ChemistB]

Your "Product Selection" page doesn't include a cell for "Type" are certain Dialyzers automatically set up with certain catheter or fistulas?

[zappabeef]

no there can be any combination of catheters and dialysers
could you also add "Access type" as a selection cell that lets the customer choose between fistuala HD, fistula hdf, catheter hd, catheter hdf

cheers,

matt

[zappabeef]

Just out of interest are you looking at a VBA code solution or an in spreadsheet formula solution?

Kind regards,

Matt

[ChemistB]

I'm looking at formula solutions

[zappabeef]

That's perfect, thank you.

let me know if I can be of any further assistance.

Regards,
Matt

[ChemistB]

Okay, take a look at what I've done so far. I created a new tab called Lists which had unique values for some of your different parts. The Data Validation Dropdowns on "Product Selection" use these. You can hide this tab.

On the Combinations tab. I did two things. First, In column O, starting in O3, I used this formula

Please Login or Register  to view this content.

to autogenerate your combination part numbers. We can move it into Column N, I just wanted to make sure that they matched the values in N before I did that.

Second, in Column P, I created a concatination of all the important fields in your product selection so that we can search that column to see if that particular combination already exists. If it does, it returns the appropriate combocode to cell B13 on "Product Selection" using this formula

Please Login or Register  to view this content.

In "Product Selection" I used Dropdowns and Index formulas to fill in all the fields. I used VBA to convert the Yes/No's of Ultra Steri and SaniCloth to values. Alternately, we could just use the values in the dropdown. Up to you.

Next step, if all this seems okay. Would be to add VBA so that if there is no match in Combinations for your product selection, a new row is added to combinations and a new Part number is generated. Is that how you see this working?

[zappabeef]

Chemist this is exactly what we were looking for. Thank you for helping us get to this point its much appreciated.

No selection Match = new row and new code
Yes that makes sense and would be very useful for our operations team.

As you may have guessed these products are for a particular dialysis machine. I have in front of me a list of products for another machine that we have not yet compiled a combination list to yet or even generated part codes for. Is there a formula that could convert the data from a list such as in the list tab to data in the combinations tab?

I am just about to leave my office and head home so will compile a list of products when i get back later this evening (its 6pm here in the UK!)

The spreadsheet looks fantastic so far. I really appreciate what you have done


Regards,

Matt

[zappabeef]

It took me longer than expected.....!

I have attached another spreadsheet based on the same format. I would ideally like to turn the lists from the list tab into a database of combinations like in the previous workbook. Is there a formula that list every possible combination.

In terms of the drop down boxes in the product selection is there a way these can be contextual. In other words the lists narrow down as machine, access type is selected. Underneath the main product list I have sorted how the lists differ according to the machine and access type.....I hope this makes sense.

thirdly a function to create a new row and product code would be ideal. I would be interested to see whether you think I could combine the workbooks so that the final product is a spreadsheet that allows you to select all machines and their consumables.


Regards,

Matt

[zappabeef]

just to be more clear on the contextual selection bit.

I have only specified the categories whereby the parts differ depending on the machine or access type. The categories I haven't included and all their parts should be available for selection regardless of previous selections

e.g. If i choose machine F I should then have the choice of all access types, dialysers etc but it should then mean only BiCarb Bag 650 and 900 can be selected from concentrate
Then when i select an access type such as catheter it should restrict my choice further by returning needles as N/A and offering Taurolock instead of chloroprep.

hope this makes sense

Regards,

Matt