0

First of all I don't have experience with Excel scripting, just basic formulas. I've seen this other question on SU but I'm not sure if that's exactly what I need or how to adapt it to my needs.

Here's what I'm trying:

I have a spreadsheet with 3 columns and 10 rows, each of them having unique values. For example:

Column A
--------
White 
Black 
Blue 
etc (until 10 items)

Column B
--------
USA
Canada 
Mexico 
etc (until 10 items)

Column C
--------
English
French 
Spanish 
etc (until 10 items)

So in total there are 30 elements. I would like to create a new table with those 3 Columns and 1000 rows, where each row would have a unique combination of those 3 elements (so 2 rows will never have the same 3 elements). Just for clarification, the order of the Columns has to be the same (A, B, C) and of course elements of one column always stay in that column.

Examples of resulting rows:

White | USA | English
White | USA | French
Black | USA | English
Black | Canada | English
... up to 1000 combinations

Any help would be appreciated!

EDIT

The point of this question is: 1. Understand if the solution provided in the linked answer applies to my case 2. Know if there a re other ways of solving this that aren't scripting (formulas, excel tools, etc)

6
  • So you basically want all permutations correct?
    – Eric F
    Jan 14, 2015 at 21:24
  • It IS the same question. If you don't understand the answer, star ta question, but make the question specific to what it is you're getting stuck on while implementing the suggested solution (instead of just asking the same thing again and just saying you don't understand the answer). Include your research beyond just linking a question. IE: What did you try already? Where exactly are you getting stuck? If you'd just like newer/different answer on the existing question, place a bounty on it to draw attention. Jan 14, 2015 at 21:29
  • @Eric F correct
    – Albert
    Jan 14, 2015 at 21:34
  • @TECHIE007 well I mention in my Q that I have no experience using scripting, so the that answer doesn't help me much. I was hoping that there are other ways of doing this that don't involve scripting (like formulas, some tool I don't know in excel, etc). That question also states that he has 3 different tables with the info in one column, I only have one table with 3 columns. So as you can see, for an experienced excel user that might be an obvious same question, but for non-experienced users that might not be so obvious. So here you have 2 different reasons why I think my question is valid.
    – Albert
    Jan 14, 2015 at 21:41
  • Can you just clarify from where you get your figure of 1000? If, as you say, the " elements of one column always stay in that column", doesn't that result in a total number of possibilities of only 100, not 1000?
    – XOR LX
    Jan 15, 2015 at 9:34

1 Answer 1

2

In the answer below I assume that your initial data is in cells A1:C10 and don't have headers. The solution doesn't use scripting. Only Excel formulas are used.

  1. Enter the following formula =MOD((ROW()-1),10)+1 in cell D1
  2. Enter =MOD(INT((ROW()-1)/10),10)+1 in cell E1
  3. Enter =MOD(INT(INT((ROW()-1)/10)/10),10)+1 in cell F1
  4. Select cells D1:F1 and drag it down to row 1000 to fill area D1:F1000 with unique combinations of indexes.
  5. Enter =INDEX(A$1:A$10,D1) in cell G1
  6. Select cell G1 and drag it to the right to cell I1
  7. Select cells G1:I1 and drag it down to row 1000 to fill area G1:I1000 with unique combinations of initial data

Explanation:

Formulas in cells D1, E1, and F1 compute the number of hundredth, tenth, and ones in the current row number (The number of row is adjusted by -1). This gives an array of unique indexes. Function INDEX(range,row_num) returns value from range given row number.

2
  • @Awsek thanks so much for this, I'll give it a try as soon as I can!
    – Albert
    Jan 15, 2015 at 16:54
  • @Awsek IT WORKS!!! thanks so much for your help, I really appreciate it :)
    – Albert
    Jan 15, 2015 at 20:55

Not the answer you're looking for? Browse other questions tagged .