Introduction

Another question from Quora.com that I answered and that I thought was interesting enough to share here.

Imagine you have a table with five columns

Each cell in each column can contain the number 1 or 2 or 3

The question is, how to find all possible combinations of numbers from the five columns?

In case it is not clear, the numbers you get from the entire set of combinations includes:

11111
11112
11113
11121
11122
11123
11131
11132
11133

In total, there are 243 combinations of numbers, which is 3^5: that is, three numbers in each column, to the power of the number of columns.

You can download my completed file and the good news is that there are quite distinct patterns to the table and what I call my number extractions. This means it is a simple problem for anyone who can unravel the patterns! If you find this difficult, why not start with 3 numbers per column and two columns? Then try three columns, four columns and then five columns. You could always move up to six, seven and more columns if you wish. While you are thinking about it, change from three to two numbers per column, then increase it to four, five … whatever you like.

I have included a few notes in my file and you will see it calls upon very few Excel functions to achieve the full solution: that’s good news, surely! Here are two screenshots to help you on your way:

This tab, intro, establishes the decision tree and its logic for you and it gives examples of the number combinations you can find. This tree is NOT finished on this tab.

The following screenshot shows the top part of the finished decision tree, the number combinations in logical order and the numbers concatenated using the CONCAT function and finally, the numbers as values.

Supplementary

Suppose the numbers are not 1, 2 and 3 but, say, 2, 6, 8 or even 12, 24, 101 … How can you cope with that? Think about it and the solution is pretty simple, too! I have not included this supplementary in this version of my Excel file.

 

There you are: take the file and see what you can do with it! kristy_quora_combo

 

Duncan Williamson

24th July 2018

 

%d bloggers like this: