V Stack Filter with condition

[yarlachiru]

I have a list of work order numbers, what i want to achoeve is in my new sheet, get the list of all the unique work orders which are only starting with number "7".
How do i achieve this
one more thing is when i use this in a fresh sheet the vstack function is working but when used in a sheet with some data it is showing #SPILL error,

I know this might be a simple question, but since Vstack and Hstack are new function which are introduced recently , Chatgpt doesnt have answer because these are included after Sep-2021

can any has answer?

[AliGW]

Why ChatGPT? It makes a lot of moistakes.

Try this:

=LET(l,IFERROR(--LEFT(Sheet1!A:A,1),0),SORT(UNIQUE(FILTER(Sheet1!A:A,(ISNUMBER(l))*(l=7)))))

[yarlachiru]

Hi Ali, thank you so much, it's working perfectly. And yes, you are right; I can definitely say that ChatGPT is not as accurate as our forum

If you can spare one more minute, could you please show me how to get this done using Vstack filter formula? I am just learning about this function and would like to use it for my future assignments. Thanks in advance!

[AliGW]

You don’t need VSTACK for this - you only have one array, so it would be superfluous to requirement.

[yarlachiru]

oh, ok, thank you

[Glenn Kennedy]

Shorter, calling fewer functions:

=LET(A,Sheet1!A1:A250,SORT(UNIQUE(FILTER(A,LEFT(A,1)="7"))))

note that using A:A will be SLOW as Ali's formula will calculate 1,000,000+++ rows, multiple times. Use something sensible but future-proof.

[yarlachiru]

Thank you Glenn