0

I am trying to create a product list using Google Sheets. Each type of product has a number of attributes (or variations), that are combined together to create a single product.

For instance, there is

  • diameter: 1/4 inch, 1/2 inch, 3/4 inch, etc.
  • length: 1/2 inch, 1 inch, 1 1/2 inch, etc
  • Material: Steel, Stainless Steel
  • Etc.

A particular product is a particular combination of these variations. So, for example:

  • 1/4 inch X 1/2 Inch Steel Carriage Bolt
  • 1/4 inch X 1 Inch Steel Carriage Bolt
  • 1/4 Inch X 1 1/2 Inch Steel Carriage Bolt
  • Etc.

What I want to do is create a number of columns just with the attributes. So a Diameter Column, a Length Column, Material Column, etc.

I then want to create a new sheet by combining these various columns together into all of the possible combinations.

Is this possible? If so, how?

Thanks.

NOTE: I do NOT currently know how to use the Google Sheets API. If need be, I can learn (or hire a developer). But, if it is possible to do this without any coding, that would be preferable (for now, at least).

Clarification Here is what I mean by creating a number of columns just with the attributes.

Column A .   Column B .   Column C
Diameter .   Length .     Material
1/4 .        1/2 .        Steel
1/2 .        1 .          Stainless Steel
3/4 .        1 1/2
1 .          2
Etc.         Etc.
Moshe
  • 6,011
  • 16
  • 60
  • 112
  • Can I ask you about `create a number of columns just with the attributes`? Especially, I cannot understand about `create a number of columns`. For example, how about creating the drop down list of `diameter`, `length` and `Material`, and creating new sheet using a script after those were selected? If I misunderstood your question, I apologize. – Tanaike Aug 18 '19 at 05:39
  • I clarified my question. In terms of `creating a drop down list` - i want ALL combinations to be generated. Please let me know if you need further clarification. Thanks. – Moshe Aug 18 '19 at 06:31
  • Thank you for adding the information. From your additional information, I think that the dropdown list can be used for your situation. After those values were selected, I think that it is required to use a script because new sheet is created. By the way, will you use Google Apps Script as the script? – Tanaike Aug 18 '19 at 07:46
  • 1
    Isn't https://stackoverflow.com/questions/54856963/mix-data-of-multiple-columns-into-one-column addressing your question? If not, can you please share a copy of your spreadsheet with the expected outcome? – Benoît Wéry Aug 18 '19 at 09:09
  • thanks - that looks promising. I'll check it out. – Moshe Aug 18 '19 at 20:33

1 Answers1

0

You can create this with apps script. Go to tools --> Script. Give your project a name and delete everything you see in the script (function myFunction) en replace it with this code below. Change the nessesery ranges and sheet names.

function combinations() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('input'); //Choose your own input tab
  var output = ss.getSheetByName('output'); //Choose your own output tab, and change it in script line 17
  var rangeA = sheet.getRange('A1:A3').getValues(); //Change the desire range
  var rangeB = sheet.getRange('B1:B3').getValues(); //Change the desire range
  var rangeC = sheet.getRange('C1:C3').getValues(); //Change the desire range
  var data = [];

  for(var i=0; i < rangeA.length; i++){
    for(var j=0; j < rangeB.length; j++){
      for(var k=0; k < rangeC.length; k++)
       data.push([rangeA[i]+" X "+rangeB[j]+" "+rangeC[k]]);
    }
  }

  output.getRange(1, 1, data.length, 1).setValues(data);
}

And then click the little play button above the script.

RemcoE33
  • 1,551
  • 1
  • 4
  • 11