Forum Discussion
Excel Filter Function - Use Same Formula Across Multiple Cells
Conceptually, you appear to be looking to produce an array or arrays which, sadly, Excel does not traditionally support. As was suggested, the TRANSPOSE function would allow your list of matched country names to spill horizontally, so allowing multiple lists to be stacked vertically. This would, though, require the formula to be copied down.
Dressed up, the same formula could be written in the form
= LET( matches, ISNUMBER(SEARCH(@PartialName, Map[Name])), matchingNames, FILTER(Map[Name], matches,"Not Found"), TRANSPOSE(matchingNames) )
Conceptually, you appear to be looking to produce an array or arrays which, sadly, Excel does not traditionally support. As was suggested, the TRANSPOSE function would allow your list of matched country names to spill horizontally, so allowing multiple lists to be stacked vertically. This would, though, require the formula to be copied down.
Dressed up, the same formula could be written in the form
= LET(
matches, ISNUMBER(SEARCH(@PartialName, Map[Name])),
matchingNames, FILTER(Map[Name], matches,"Not Found"),
TRANSPOSE(matchingNames) )PeterBartholomew1 mtarler JMB17
Hi All,
Really appreciate your comments. Using Transpose with the formula copied down a couple of hundred rows did the job.
One thing to note, is that in my scenario I needed to Protect the sheet, to stop users messing around with the structure. In doing so, when using the filter functionality the search will chug along (my laptop is Excel 64, I7, 16GB RAM). I won't be telling users that they can filter do to this performance issue and just use the drop down.
Thanks for all your help!
I wouldn't have anticipated problems especially with the machine spec you outline.
In my case, I asked for trouble by importing geographical data from the internet, so other things com into play.
