Forum Discussion
Extracting table values if values in two columns have identical values in other rows
As variant
=LET(
table, $B$3:$E$41,
product, $C$3:$C$41,
decrement, $D$3:$D$41,
VSTACK(
{"ID NUMBER","PRODUCT","DECREMENT","IN USE?"},
SORT( FILTER(table, COUNTIFS(product, product, decrement, decrement) > 1 ),
{2,3}
)
)
)
You went further than I did!
= LET(
multiplicity, COUNTIFS(product, product, decrement, decrement),
FILTER(Table1, multiplicity > 1)
)A thought to throw into the discussion though. I have reservations concerning lines such as
table, $B$3:$E$41,As part of a LET statement, I suspect a copy of the table is loaded into memory at least temporarily as 'table'. If a defined name is used instead, that does nothing until the name appears in a formula where the result is calculated without making a copy of the range?
I'm not sure there is any difference except maintenance. My understanding is that defined within LET name is called only with function evaluation, and only here we load the value into the memory. Not with definition.
That sounds pretty likely. What triggered the thought was a complaint on Mynda Treacy's blog that suggested a large workbook had suffered considerable 'bloat' on introducing LET.
I also have problems with estimating requirements for thunks. Depending upon whether it is the formula or its calculated result that is stored can make a substantial difference to both processing time and memory demand (one improves at the expense of the other)
I guess Charles Williams shall know that in details.
Thunk is just the pointer on the allocated memory, perhaps it's allocated statically or dynamically depends on situation.
Anyway, here are two options. If someone knows the answer and share it. Or we dig with the investigations. Otherwise only guesses.