Excel Filtered Values to Array

Question

Given an Excel table of following structure:

Col.A  Col.B  Col.C  Col.D  Col.E  Col.F  Col.G
2      3      2      5      2      2      
5      5      2      5      5      5      5

The formula I'm using in E1 is

=FILTER($A1:$D1,$A1:$D1=2)

and in E2

=FILTER($A2:$D2,$A2:$D2=5)

Excel returns the values in different cells E1, F1 and E2, F2 and G2.

Is it instead possible to store e.g. the outputs of the formula in E1 as an array in cell E1 itself in the shape of

{2;2}

and for E2

{5;5;5}

that can be further analyzed with a formula like

=SUMPRODUCT({2;2}=2)

?

Ideally, without the use of an array formula.

I know that I can reference the cells directly using

=SUMPRODUCT($A1:$D1=2)

but this is not what I am trying to achieve here.

Thanks in advance.

no a cell cannot hold an array. It can only hold a single value. That value can be a string that looks like an array but it would then need to be parsed to create the array. just wrap the filter in the SUMPRODUCT: `=SUMPRODUCT(--(FILTER($A1:$D1,$A1:$D1=2)=2))` — Scott Craner, Aug 24 '21 at 16:25
Or, you can technically do: `=SUMPRODUCT(--(E2#=2))` But I figured you do not want the spill to happen. — Scott Craner, Aug 24 '21 at 16:27
@ScottCraner Thanks. I tried to build the array with ="{"&TEXTJOIN(",",TRUE,FILTER($A1:$D1,$A1:$D1=2))&"}" but then wasn't able to use it as such. What do you mean by "parsing"? — Dorian IL, Aug 24 '21 at 16:31
Then just put the FiLTER formula in the formula where you want the array to be used. Making it a string with TEXTJOIN then parsing each part back out with some other function like MID or FILTERXML is slow. — Scott Craner, Aug 24 '21 at 16:33
You are basically in the first step with TEXTJOIN, turning it into a string that looks like an array. Then you would need to split that string on the `,` with either MID or FILTERXML to turn it back into an array to be used in a formula. Where you can skip all that and just use the same FILTER function to return the array to any other function off the original data. — Scott Craner, Aug 24 '21 at 16:40

Scott Craner · Accepted Answer · 2021-08-24T17:37:19.777

No, there is no way to make a single cell in Excel hold an array. That is why they invented the spill.

By doing things like:

 ="{"&TEXTJOIN(",",TRUE,FILTER($A1:$D1,$A1:$D1=2))&"}"

OR

 =ARRAYTOTEXT(FILTER(A1:D1,A1:D1=2),1)

May create something that looks like an array ie {2,2} it is in fact creating a text string not an actual array. This text string cannot easily be converted to an array.

To do so would require parsing the string. Using MID,LEFT,RIGHT or FILTERXML to split the string on the , and extract each part to turn the text string back into an array. For EXAMPLE

This process, while possible require long formula and over complicate the use.

Instead just nest the FILTER in the formula required ie:

=SUMPRODUCT(--(FILTER($A1:$D1,$A1:$D1=2)=2))

Now we avoid the need to create a string and parse it, which adds computation cycles and over complicates the formula.

It also has the benefit of always referring to the original values.

Excel Filtered Values to Array

1 Answers1