Stuck in REDUCE/VSTACK solution

Question

=LET(
    data, jp!A2:C8&"",
    uniqueLetters, UNIQUE(INDEX(data,,1)),
    result, REDUCE("", uniqueLetters, LAMBDA(r,letter,
        VSTACK(r, "", FILTER(TAKE(data,,2), (INDEX(data,,3)="a")*(INDEX(data,,1)=letter)))
    )),
    result1, DROP(result,2),
    result2, MAP(result1, DROP(result,1), LAMBDA(a,b,
        IF(AND(ISNUMBER(a), ISNUMBER(b)), IF(a=b, "", a), a)
    )),
    IFERROR(DROP(result2,-1),"")
)

Hello, this formula is to copy and paste data from jp!A2:C8 based on the value of jp!C1:C8. In this case the value of jp!C1:C8 is "a". The formula isn't working the way I want it to be. I have two favors to ask:

One, when jp!C1:C8 contain values other than "a", the formula won't work. Realistically En!C1:C8 has "a", "b", "c" for my other sheets to copy. Is there a way to solve this?

Two, when there is a duplicate name in jp!A2:A8, then the formula will change the duplicate to ". (I originally made this to work, but then I somehow messed it up and don't know how to fix it.)

Basically I want it to work like it's shown in the pic.

I hope this all make sense. Thank you so much!

Answer 1

Here another alternative using the REDUCE/VSTACK pattern (1):

=LET(in,A1:C7,C,TAKE(in,,-1),lk,"a",f,FILTER(DROP(in,,-1),C=lk),fa,TAKE(f,,1),
 DROP(REDUCE("", UNIQUE(fa), LAMBDA(ac,u, LET(ff, FILTER(f, fa=u),
  ffx,DROP(ff,1), first,INDEX(ff,1,),
  out, IF(ISERR(ffx),first,VSTACK(first,
   HSTACK(IF(TAKE(ffx,,1)=u,""""),TAKE(ffx,,-1)))),
  VSTACK(ac, IF(ROWS(ac)=1, out, VSTACK({"",""},out)))))),1))

Here is the output for a and b: changing the lk value for the case of b:

To return an empty string instead of zero in Dog row for the case of b, check my answer to the following question: How can I return blank cells as it is instead of printing as Zeros while using filter function.

The formula only depends on two input range values in and lk, so it is easy to maintain and use in other sheets. The name f filters the first two columns of the input in where the lookup value (lk) match the third column of the input (C). Then we use the REDUCE/VSTACK pattern (1) to iterate over unique values of the first column of the filtered values (fa). The name ff filters the name f where the first column is equal to the unique value u on each iteration.

From there it uses LET to define additional names: first, the first row of ff and ffx the rest of the rows. Keep in mind that it is possible there is only a single row in ff, in such case ffx is equal to #CALC!. We consider this scenario via ISERR call. If ISERR is TRUE it just returns the first row (first), otherwise the first plus the following rows replacing the first column with "" this is how we do it:

HSTACK(IF(TAKE(ffx,,1)=u,""""),TAKE(ffx,,-1)))

It uses the condition TAKE(ffx,,1)=u which is always TRUE on each iteration to generate a constant array with "" values.

Now the name out has the desired output per iteration. The only additional step required is to add an empty row ({"",""}) at the beginning on each iteration, except for the first one. If you add the empty row at the end on each iteration, you don't need this condition, but it will generate an unnecessary empty row in the last iteration, so I prefer to avoid that. The condition to identify the first iteration is ROWS(ac)=1.

(1): how to transform a table in Excel from vertical to horizontal but with different length

Answer 2

There's two perfect solutions already. I wanted to share yet another approach since it may be easier to read:

=LET(data,      A2:C8,
     condition, "a",
     f,         FILTER(data,TAKE(data,,-1)=condition),
     a,         INDEX(f,,1),
     b,         INDEX(f,,2),
DROP(   REDUCE( 0, UNIQUE(a),
        LAMBDA( x, y,
           LET( z, FILTER(b,a=y),
                VSTACK( x,
                        {"",""},
                        IFERROR(HSTACK(y,z),""""))))),
     2))

It first filters the range for rows that meet the condition.

Then reduce iterates through the unique values of the (filtered array f) first column.

For each unique value it stacks the previous result, a blank row of 2 cells and a horizontal stacking of the unique value and the filter of values from b for that unique value.

Because the filter may return multiple rows, while the unique value being stacked is only one value, the HSTACK result would contain an error value below the unique value, if the filtered range exceeds 1 row. IFERROR turns that into ".

Then we have the result with 2 leading rows: The first two rows are triggered by the first unique value of a (or y for the matter of REDUCE) in REDUCE. They trigger to stack the start value x (0) and a row containing blanks ({"",""}) with the first filtered result.

If we drop these leading two rows, we have the end result.