How can I filter zeros from a dynamically generated array in Excel?

Question

I'm trying to generate a list of all the factors of a number in MS Excel. I have the following formula:

=NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12))

This gives me an array with factors and zeros, as below:

1 2 3 4 0 6 0 0 0 0 0 12

But I can't seem to find a formula to remove the zeros. I've tried different permutations of LET, LAMBDA, LARGE, FILTER, SORT, and others. Nothing works. Any tips on what I could be doing wrong? I want to do this without VBA.

Answer 1

This lists numbers from 1 to 12, checks which divide 12 evenly, and filters out the rest. The formula repeats the expression to test which of those numbers are not zero; that’s how FILTER() knows which entries to include in the final list. Will work in your scenario.

=FILTER(NOT(MOD(12,SEQUENCE(1,12)))*SEQUENCE(1,12),(NOT(MOD(12,SEQUENCE(1,12)))*SEQUENCE(1,12))<>0)

Answer 2

filter() will work, but you will have to enter your formula twice:

=FILTER(NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12)),NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12))<>0)