How to combine data set to create unique combinations

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)

Albert

Posted 2015-01-14T21:22:41.740

Reputation: 203

Question was closed 2015-01-20T00:15:39.307

So you basically want all permutations correct? – Eric F – 2015-01-14T21:24:07.113

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.

– Ƭᴇcʜιᴇ007 – 2015-01-14T21:29:05.523

@Eric F correct – Albert – 2015-01-14T21:34:51.147

@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 – 2015-01-14T21:41:31.270

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 – 2015-01-15T09:34:29.903

@XORLX maybe that way of saying it wasn't the best, you're right, but yes, if you combine 3 elements that have 10 possible values the total number of unique combinations is 1000. Think of it as a number of 3 figures, each figure with possible values 0-9 – Albert – 2015-01-15T16:53:18.440

Answers

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.

Avsek

Posted 2015-01-14T21:22:41.740

Reputation: 58

@Awsek thanks so much for this, I'll give it a try as soon as I can! – Albert – 2015-01-15T16:54:31.350

@Awsek IT WORKS!!! thanks so much for your help, I really appreciate it :) – Albert – 2015-01-15T20:55:25.633

Asked: 2015-01-14T21:22:41.740

Viewed: 6 418 times

Active: 2015-01-15T14:51:01.237