excelfunctionexcel-formulafilter

Excel Filter Function On A Stacked Return

I'm stacking multiple columns containing same/similar data into one column. I'm also using the Filter function to then select a specific column as the output, using the following Excel Formula:

=FILTER(LET(data,A2:O26,cols,5, rc,ROWS(data), ss,COLUMNS(data)/cols, ri,WRAPROWS(TOCOL(IF(SEQUENCE(,cols),TOCOL(IF(SEQUENCE(,ss),SEQUENCE(rc)),,1))),cols), ci,WRAPROWS(TOCOL(TOCOL(IF(SEQUENCE(rc),SEQUENCE(,cols)))-1+SEQUENCE(,ss,,cols),,1),cols), r,INDEX(data,ri,ci), r),{0,0,1,0,0})

I want the returned output of the formula to ignore the blanks (i.e. <> ""). I am unable to attach a CSV file, so I am providing a photo of the data range below.

Data Range

The resultant output I have so far does not ignore the blanks cells. This is the output I have up to this point:

Output of Formula thus far

GNI GNI PPP SCHOOL TD GDP GNI GNI PPP SCHOOL TD GDP GNI GNI PPP SCHOOL TD GDP
a 1 2 3 4 5 r 1 2 3 4 5 sdf 1 2
b 1 2 3 4 5 g 1 2 3 4 5 sdf 1 2
c 1 2 3 4 5 h 1 2 3 4 5 dfhg 1 2
d 1 2 3 4 5 jk 1 2 3 4 5 fgh 1 2
e 1 2 3 4 5 k 1 2 3 4 5 ghj 1 2
f 1 2 3 4 5 l 1 2 3 4 5 ret 1 2
g 1 2 3 4 5 u 1 2 3 4 5 1 2
h 1 2 3 4 5 y 1 2 3 4 5 1 2
i 1 3 4 5 t 1 2 3 4 5 1 2
j 1 3 4 5 r 1 2 3 4 5 1 2
k 1 3 4 5 e 1 2 3 4 5 1 2
l 1 3 4 5 w 1 2 3 4 5 1 2
m 1 3 4 5 q 1 2 3 4 5 1 2
n 1 3 4 5 e 1 2 3 4 5 1 2
o 1 3 4 5 d 1 2 3 4 5 1 2
p 1 3 4 5 f 1 2 3 4 5 1 2
q 1 3 4 5 gbv 1 2 3 4 5 1 2
r 1 3 4 5 d 1 2 3 4 5 1 2
s 1 3 4 5 s 1 2 3 4 5 1 2
t 1 2 3 4 5 df 1 2 3 4 5 1 2
u 1 2 3 4 5 gf 1 2 3 4 5 1 2
v 1 2 3 4 5 g 1 2 3 4 5 1 2
w 1 2 3 4 5 d 1 2 3 4 5 1 2
x 1 2 3 4 5 sd 1 2 3 4 5 1 2
y 1 2 3 4 5 s 1 2 3 4 5 1 2

I'd love to attach the example CSV file, but I just can't find an attachment tool.

Solution

  • Try using the following formula:

    =LET(
     a, FILTER(A2:O26,A1:O1="SCHOOL"),
     TOCOL(IFS(a<>"",a),2,1))