I currently use this Excel formula to give me the possible combinations from 2 sets of data. With the combinations, I can easily allocate them to various other breakups or sub-divisions.
Example:
My reporting levels are divided into country and division.
Countries
1. Canada
2. USA
3. UK
Divisions
1. Retail
2. Service
3. Warehouse
Enter the data above into a Excel sheet. Refer to the diagram below
Copy and paste the formula below into cell E2
=IF(ROW()-ROW($C$2)+1>COUNTA($A$2:$A$4)*COUNTA($C$2:$C$4),””,INDEX($A$2:$A$4,INT((ROW()-ROW($A$2))/COUNTA($C$2:$C$4)+1))&INDEX($C$2:$C$4,MOD(ROW()-ROW($A$2),COUNTA($C$2:$C$4))+1))
Drag the formula down to cell E3 and so on until the result in the cell is blank.
Refer below to calculate the number of possible combinations using this formula:
Number of countries multiple number of divisions (no. of countries X no. of divisions)
In this case, computation for number of combinations is per the following:
3 * 3 = 9
I hope this Excel formula has been helpful.