4
=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. enter image description here

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

P.b
  • 8,293
  • 2
  • 10
  • 25
Chi Wong
  • 57
  • 3
  • You are referring to `jp!` sheet, but in the picture it refers to `En` sheet. I thought you are trying to filter the input and sum the values for each animal, but in `sheet a` `Cat` has the value `11`, instead of `66`. How do you want to count the blank line with no letter? What are you trying to achieve, sometimes it is better to understand the problem than somebody else formula. – David Leal Jun 17 '23 at 22:56
  • 1
    Thank you for telling me this. I always mess up simple mistake like this. – Chi Wong Jun 18 '23 at 12:52

3 Answers3

4

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))

enter image description here

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.

P.b
  • 8,293
  • 2
  • 10
  • 25
  • 1
    we took similar approach, but you really simplified it, great solution. I think you can simplify it more, you don't need to define `c`, it is not used. You always generate the blank line (even when there is only one iteration, not a big deal), I was trying to avoid it, but it makes the formula longer. – David Leal Jun 18 '23 at 15:01
  • 1
    Good point on `c`. I initially started an approach not filtering the range. Then I realized filtering it prior to splitting the data was a better approach and did not realize it could be removed. Removed it now. Good catch. – P.b Jun 18 '23 at 15:05
  • 1
    The other approaches (both yours, @DavidLeal and @Vbasic2008) are very creative and useful as well. Nice to see how many different approaches can lead to the same. – P.b Jun 18 '23 at 15:10
3

Filter Data

enter image description here

=LET(data,A2:C17,sheet,"a",rep,"""",
    a,TAKE(data,,1),l,TAKE(data,,-1),
    au,UNIQUE(TOCOL(a,3)),
DROP(DROP(REDUCE("",au,LAMBDA(rr,r,LET(
    f,FILTER(data,(a=r)*(l=sheet),""),
    fr,ROWS(f),
    ff,IF(fr=1,f,HSTACK(VSTACK(r,INDEX(rep,SEQUENCE(fr-1,,1,0))),DROP(f,,1))),
IF(COLUMNS(ff)>1,IFERROR(VSTACK(rr,ff,""),""),rr)))),1),-1,-1))
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • thank you so much! yours works so well for me too. I'm going take this and try to incorporate a funtion where if the extracted data falls between a print area(like between page 1 and page 2), then the formula will put those data on the top of page 2. It's gonna be a lot of work for me lol. thank you again! – Chi Wong Jun 18 '23 at 23:30
3

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: output

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

David Leal
  • 6,373
  • 4
  • 29
  • 56