Duplicate non empty rows and add a new column in a Google Sheet-CodePudding

I have a table with some data. The sheet in question does have empty rows.

Sheet1:

date	id	name	col1	col2	col3
2022-01-01	31	one	yes	1	5
2022-02-01	26	two	yes	2	4
2022-03-01	150	three	no	3	9
...	...	...	...	...	...
[empty row]
[empty row]

For each row, I have two metrics:

is good = col1 = yes
% complete = col2 / col3

I want to use an ARRAYFORMULA or something to split each row into two rows, one for each metric. So, for example, the expected output is:

date	id	name	metric	value
2022-01-01	31	one	is good	true
2022-01-01	31	one	% complete	.2
2022-02-01	26	two	is good	true
2022-02-01	26	two	% complete	.5
2022-02-01	150	three	is good	false
2022-02-01	150	three	% complete	.33

I came up with this formula, but it puts a lot of blank lines in the middle since the source data has empty rows.

=ARRAYFORMULA(
    {
        IF(
            Sheet1!A2:A = "",
            ,
            {
                Sheet1!A2:C,
                Sheet1!D2:D = "yes"
            }
        );
        IF(
            Sheet1!A2:A = "",
            ,
            {
                Sheet1!A2:C,
                Sheet1!E2:E / Sheet1!F2:F
            }
        )
    }
)

My source data has a lot of rows, so I'm trying to find an efficient way to do this. I also considered ARRAYFORMULA with column concatenation (like an unpivot) but it's horribly slow because I have so much data.

My source data also has a lot of columns that translate to 10 different metrics/rows.

I thought to try something like this so I'm only processing rows with data, but it results in a Formula parse error.

=ARRAYFORMULA(
    IF(
        Sheet1!A2:A = "",
        ,
        {
            Sheet1!A2:C,
            Sheet1!D2:D = "yes";
            Sheet1!A2:C,
            Sheet1!D2:D = "yes";
        }
    )
)

Updated: I have created a sheet with my example data:

update 2:

=LAMBDA(a, b, c, d, e, SORT({
 FILTER({a, IFERROR(N(b)/0, "is good"), c="yes"}, b<>"");
 FILTER({a, IFERROR(N(b)/0, "% complete"), d/e}, b<>"")}))
 (Sheet1!A2:C, Sheet1!A2:A, Sheet1!D2:D, Sheet1!E2:E, Sheet1!F2:F)

CodePudding user response：

Use REDUCE to loop and create two rows for every row:

=REDUCE(
  {Sheet1!A1:C1,"metric","value"},
  Sheet1!A2:INDEX(Sheet1!A2:A,COUNTA(Sheet1!A2:A)),
  LAMBDA(
    a,c,
    {a;
    OFFSET(c,0,0,1,3),"is good", OFFSET(c,,3,1,1)="yes";
    OFFSET(c,0,0,1,3),"% complete", QUERY(OFFSET(c,0,4,1,2),"select E/F label E/F ''",0)
    }
  )
)