Oh, only a couple of seconds for 49 formula instances--good to know.
An hour to calculate 1500 is a bit more cumbersome. Thanks for running
the test!
You're very welcome for the formula. It was an interesting challenge.
I noticed a simplification for the 4 sections that evaluate previously-
unused/used stores. Instead of:
MMULT ... <> ... = COUNTA($E$1:$E4)
MMULT ... <> ... <> COUNTA($E$1:$E4)
MMULT ... <> ... = COUNTA($E$1:$E4)
MMULT ... <> ... <> COUNTA($E$1:$E4)
One may use:
MMULT ... = ... = 0
MMULT ... = ... > 0
MMULT ... = ... = 0
MMULT ... = ... > 0
........still don't know how it works.
I'll leave my notes on the formula, in case you want to dissect.
[I'll assume you know the uses of not-equal-to (<>), exponentiation
(^), and arrays { }. And I'll assume you know, or could look up, how
comparing an array with a value generates an array of Trues and
Falses, that you can convert those to numbers by multiplying by
numbers, or must sometimes convert to 1's and 0's by doubly-negating
(--), that multiplying (*) two horizontal (or vertical) arrays of the
same length results in a horizontal (or vertical) array; but two
arrays of different lengths and same orientation can be multiplied (*)
or matrix-multiplied (MMULT) if the orientation of one is transposed
(the result being a rectangular array); that in using MMULT, the row
entries of matrix1 are multiplied by the column entries of matrix2 and
summed to form a single entry in the resulting array.]
*Current test formula, omitting the IsError function*
=INDEX($A$9:$A$12,MATCH(
MIN(
($B5=$B$9:$B$12)*ABS($C5-$C$9:$C$12)*
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)=0)+
999999999*(($B5<>$B$9:$B$12)+
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)>0))
),
($B5=$B$9:$B$12)*ABS($C5-$C$9:$C$12)*
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)=0)+
111111111*(($B5<>$B$9:$B$12)+
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0)>0)),
0))
*Formula*
IsError (Index( StoresInTable2, Match( Min(UnusedStore SalesDiffs
across CurrentState), (UnusedStore SalesDiffs across CurrentState'),
exactmatch)), "No Match")
*Errors*
IsError returns "No Match" for no match or for general snafu. Could
use IsNA to trap #N/A caused by no match, but not hide other errors
(e.g. #VALUE! caused by not array-entering formula). IsNA would have
to be nested as If( IsNA(formula), "No Match", formula), duplicating
the long Index formula.
*Arrays*
UnusedStore SalesDiffs across CurrentState:
1D array of Diffs interspersed with 999999999's in the first mention
and 111111111's in the second, but start w/Diffs and 0's:
( IsGoodState * AbsoluteDiff * IsUnusedStore)
Any diff with wrong State or with used Store is implicitly zeroed out.
($B5=$B$9:$B$12) * ABS($C5-$C$9:$C$12) * (MMULT ... = ... = 0)
Finally, Add the below array to the above to fill in zeroed diffs with
huge numbers, so MIN won't grab a filler zero instead of a sales diff.
+ HugeNumber*( IsBadState + IsUsedStore ):
1D array of 0's and huge numbers. 0's where the diffs are above, huge
numbers where the 0's are above.
+ 999999999 * (($B5<>$B$9:$B$12)+(MMULT ... = ... > 0))
or
+ 111111111 * (($B5<>$B$9:$B$12)+(MMULT ... = ... > 0))
HugeNumber:
E.g., 999999999 the first time, something different the next, e.g.,
111111111.
Each must be larger than the absolute value of the maximum Sales
figure over both tables.
Within MATCH, different HugeNumber from 1st array (inside MIN) to 2nd
array, so when no match should be available, i.e., MIN is a
HugeNumber, no match is found among the different HugeNumbers of the
2nd array.
Note: MIN Diff could still legitimately be zero, if sales1-sales2=0
for unused Store2 in a legit State. Such 0's are not replaced by huge
numbers.
*Evaluating Store list in Table2 as Unused/Used according to Previous
Formula Results in Col E*
IsUNusedStore: 1D array of True and False:
Compares the entries of the 1D array resulting from MMULT collapse to
0.
MMULT_result = 0
MMULT((EachStore <> StoresUsed), (1's list)) = 0
(MMULT(--($A$9:$A$12=TRANSPOSE($E$1:$E4)),ROW($E$1:$E4)^0) = 0)
Similarly for the T/F list IsUSEDStores, MMULT_result > 0.
(EachStore <> StoresUsed): 2D array of True and False
($A$9:$A$12<>TRANSPOSE($E$1:$E4))
Compare a long vertical list of all table2 store numbers (in A) with a
short horizontal list (need transpose) of the store numbers already
used (E), to form a 2D rectangular matrix. Each row has Trues and
Falses according to whether each store matches any already used. Up to
1 True per row.
List of 1's to collapse the 2D matrix above into a 1D list:
ROW($E$1:$E4)^0
{1;2;3;4}^0 = {1;1;1;1}
Required by MMULT.
Nice trick seen in a Harlan Grove post.
Okay, that is probably both more and less than you wanted to know, if
you wanted any explanation at all.
- David
Luckily, I only had 49 stores that I needed to copy this down (there were
over 3,000 stores where it looked for a match). Copying this down 50 rows
was fast, just a couple of seconds).
To satisfy your curiousity I ran a test last night to see what happens with
more rows. I broke my tables up into similar sizes (1,564 and 1,566 stores
respectively). After dropping the formula down 1,566 rows, it took exactly 1
hour to finish calculating. There ended up being 310 stores with no matches.
I ran this on my Dell Latitude D620 laptop (Duo Core processors, 2GB RAM).
Thanks again for the help. Very cool formula........still don't know how it
works.
..
..
..
<snipped messages>
..
..
..
