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
– Scott Craner, Commented Aug 24, 2021 at 16:25
Or, you can technically do: =SUMPRODUCT(--(E2#=2)) But I figured you do not want the spill to happen. — Scott Craner
– Scott Craner, Commented Aug 24, 2021 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
– Dorian IL, Commented Aug 24, 2021 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
– Scott Craner, Commented Aug 24, 2021 at 16:33

Scott Craner · Accepted Answer · 2021-08-24 17:37:19Z

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.

Collectives™ on Stack Overflow

Excel Filtered Values to Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related