• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a product list!

Y

Yufan Chen

New Member
Hello!

I want to submit a list of our company's products to a POS software company using Excel.

Our products have multiple variables, and instead of inputting each possible combination individually, I would like to know the optimum way of using formulas and formatting to create an adjustable list.

Example of variables involved:
1st Variable: Type of material - 4 different woods, 4 different plastics (8 total)
2nd Variable: Profile - 20 Sizes and dimensions (20 total)
3rd Variable: Color - 20 different colors (20 total)

This will result in 3,200 different combinations or 3,200 products.

Each product will include 3 fields when submitted to the POS software company:
1.) The item name/code, which represents each variable of that product (i.e. ASH-01-05: ASH=Ash wood, 01=profile #, 05=color #)
2.) The description of the product. (i.e. Ash wood, 2 x 4", White)
3.) The price. Each of the variables in a product will have a metric for pricing. (i.e. Ash = $1/ft., 2 x 4"=0.5ft., White = +25%, therefore the price of ASH-01-05=$0.625/ft.

I hope this was clear. I'm not certain how difficult, or possible, a list like this would be to create. I just know it's beyond my skills. A successfully created list would save me a lot of time when making updates to our product list. Instead of manually changing 160 items for a small change in 1 variable, I'd like to be able to make changes to a few cells and let the formulas do the rest of the work.

Thanks so much!
 
Hi Yufan ,

Can you upload a file which has all the variations for at least one product ? If there are more products , that is better.

Also , your file should contain all the item codes / names / descriptions , along with the metrics for pricing ; if you do not wish to release confidential information , put some random prices , so that testing is possible.

Narayan
 
Hello Narayan,

I've uploaded a sample list. The first spreadsheet "Data2" will be hidden, and used for cell referencing. The following number-labeled spreadsheets are what the POS Software will use. They're split into separate sheets because that's the only method I currently know how to use to make updates to multiple cells/rows/columns using select all sheets.

The items currently only include 2 different variables, Finish and Profile. The first two columns are the item codes, and the following two are descriptions--they'll be combined later on using concatenate.

I hope it's not too unclear what I'm trying to accomplish, but I can provide any needed additional information. Thanks a lot for your help!
 

Attachments

Hi Yufan ,

Sorry , but I am not able to understand your requirement ; your workbook has several individual product tabs , and a summary tab named Data 2. What is it that is required to be done with all of this information ?

Please explain.

Narayan
 
Narayan,

I've created a simpler example of what I'm trying to accomplish with our product list. It's all on one work sheet, with the 3 tables on the left containing the raw data. The table on the right is the list of individual products that are possible using the 3 tables in combination. There's not really any cell referencing, but hopefully it makes more sense now.
 

Attachments

Hi Yufan ,

Thanks. Now it is clear.

However , I think there is a correction to be made ; the +15% is shown against item code MP , whereas you have shown increased prices against item code PP.

Also , assuming that we now go back to your original uploaded workbook , do you want as output what you have now shown in the range G3 through I21 ?

Narayan
 
Narayan,

Yes, the MP and PP pricing factor was reversed. A mistake on my part.

Yes, I'm looking to have the output from table G3:I21. There might be slight alterations in format and content, but the general idea of combining different variables, each with their own list of possibilities, is what I'm attempting to achieve.
 
Narayan,

It looks like it worked. However, I've never created or ran a macro before. Can you explain to me how you did it so I can use it for the list?
 
Hi Yufan ,

If you can go through the code , understand it and modify it to suit your data , please do so.

If not , please upload your file , and I'll do whatever is required.

I'll list down all that I have done :

1. Converted the data on the Data tab to tables ; the one named Profiles_Table refers to =$A$2:$C$29 , while the one named Finishes_Table refers to =$E$2:$F$24.

2. Two named ranges have been created :

Max_Dim_Text_Length refers to =Data!$H$1

Max_Finish_Text_Length refers to =Data!$I$1

In these two cells , I have put in the following formulae :

$H$1 : =MAX(LEN(Profiles_Table[Width x Height]))

$I$1 : =MAX(LEN(Finishes_Table[Finish]))

Both of the above formulae are array-entered , using CTRL SHIFT ENTER. The basic purpose of these is to format the description in column C on the Product List tab.

The code itself is fairly simple , and uses two For ... Next loops to go through all the entries in the two tables , combining them in all the possible ways.

If you need to extend this to 3 variables , one more For ... Next loop will have to be added.

Narayan
 
Narayan,

Unfortunately, I don't have much skills in coding. Without further instruction, I probably won't be able to write and adjust the coding for the macros used. Is there a way to incorporate the For ...Next function into formulas available in Excel?

One of the goals for creating the list, is so that whenever there are small or large updates, we'll be able to make them immediately and submit those updates to the POS Software company. I truly appreciate your help thus far, but I couldn't burden you, or presume you'd be willing and accountable, for making product updates to the list for us.

The data for the list provided is not complete or entirely accurate. We're still working on inputting all of the data, which at point of completion we'll need to be able to implement the functionality you've provided through macros.

If there is no other way manageable through Excels formulas within the cells, then perhaps if I give you another list to code, then I can study the differences in the changes made to the coding and see if I'd be able to make the necessary changes on my own whilst updating the list. Thank you so much for your help so far!
 
Hi Yufan ,

Let me suggest this :

1. You see if you can arrange all the variable tables in one sheet , with their column headers in row 1 , so that all the tables go across the worksheet ; this will allow you to add rows to each table as and when necessary ; if you wish , you can keep blank columns between each table , so that in case there is any future requirement , you can easily resize the table to include the additional columns.

2. You standardize on the format of the individual data tables on each worksheet tab so that the tables start from the same cell on every worksheet , and their columns are in the same order on every worksheet.

3. Create a worksheet tab named Product List , and put in the column headers ; otherwise this will be blank ; this is the sheet where the macro will put in the complete product list combining all the variable tables , and taking the prices from the data tables in the different worksheet tabs.

4. Each time you add any new rows to any of the variable tables , or you change any of the prices , you re-run the macro ; it will erase all data on the Product List tab , and recreate it afresh.

5. Try to envisage all possibilities , so that the macro may not need to be modified in the near future.

If you can upload this template , I can rewrite the macro to use this.

Narayan
 
You must log in or register to reply here.
Back
Top