Description
The MM_MIX function performs a combination of two ranges by repeating the elements of Range2 for each element in Range1.
Syntax
=MM_MIX(Range1, Range2)-
Range1
The first range to be combined. -
Range2
The second range to be combined.
Remarks
- You can include ranges of multiple columns in each range, and even mix a single-column range with a range with several columns.
- The function is similar to the SQL 'UNION ALL' function.
- The function MM_GETRANGE will appear in adjacent cells next to the cell containing the MM_MIX function. Its purpose is to resize the grid where the MM_MIX function is applied, ensuring that the resulting data is displayed correctly across the necessary cells.
Example 1: Basic example
Consider two ranges, Range1 and Range2:
A |
B |
|
|---|---|---|
| 1 | 1 | X |
| 2 | 2 | Y |
| 3 | 3 | Z |
4 |
4 | W |
In cell D1, the formula =MM_MIX(A1:A4, B1:B4) is used.
The function combines Range1 and Range2 by repeating Range2 for each item in Range1. The result looks like this:
| D | E | |
|---|---|---|
| 1 | 1 | X |
| 2 | 1 | Y |
| 3 | 1 | Z |
| 4 | 1 | W |
| 5 | 2 | X |
| 6 | 2 | Y |
| 7 | 2 | Z |
| 8 | 2 | W |
| 9 | 3 | X |
| 10 | 3 | Y |
| 11 | 3 |
Z |
| 12 | 3 | W |
In the first column, each element of Range1 appears multiple times (once for each item in Range2). In the second column, Range2 is repeated for each element of Range1.
Example 2: Mixing a table with a range
You can also mix a table with a range. Consider the following tables:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1 | a | X | 10 | |
| 2 | 2 | b | Y | 20 | |
| 3 | 3 | c | Z | 30 | |
| 4 | 4 | d | W | 40 |
Using the formula =MM_MIX(A1:B4, D1:E4), you will mix the first two columns of the table on the left with the table on the right.
The result will combine each pair from Range1 (columns A and B) with each pair from Range2 (columns D and E):
| G | H | I | J |
|---|---|---|---|
| 1 | a | X | 10 |
| 1 | a | Y | 20 |
| 1 | a | Z | 30 |
| 1 | a | W | 40 |
| 2 | b | X | 10 |
| 2 | b | Y | 20 |
| 2 | b | Z | 30 |
| 2 | b | W | 40 |
| 3 | c | X | 10 |
| 3 | c | Y | 20 |
| 3 | c | Z | 30 |
| 3 | c | W | 40 |
| 4 | d | X | 10 |
| 4 | d | Y | 20 |
| 4 | d | Z | 30 |
| 4 | d | W | 40 |
Each combination of elements from the two ranges is displayed.