excelexcel-formula

Excel automate dynamic number of row generation from another set of data


I am trying to automate for dynamic number of rows to be created from a reference data.

So there are 2 sheets - Sheet1 and Sheet2. What I want is by referring to Sheet1 (column E i.e. ROLE_ID ) and then under Sheet2 (Column ROLE_ID), it should dynamically create that many number of rows (as there are in Sheet1 - Column E i.e. ROLE_ID)

Thus, it would first create 8 rows for GROUP A, then 5 rows for GROUP B and then 8 rows for GROUP C.

Below is how my Sheet1 looks (which is kind of my reference data);

enter image description here

and below is my Sheet2 (where I want to add the dynamic rows from Sheet1);

enter image description here

Not sure if it is possible to automate such thing using Excel formulas.

UPDATED

Below is my expected output (in Sheet2)

enter image description here

=FILTER(J4:J186, IF(ISBLANK(J4:J186), TRUE, IF(ISNUMBER(J4:J186), TRUE, IF(ISNA(J4:J186), FALSE, TRUE))))

=LET( a, Sheet1!K4:K186, b, TAKE(a,,-1), c, SCAN(0,b,LAMBDA(x,y,IF(y<>0,x,x+1)))+1, d, Sheet2!C7:F36, IF(b="","",HSTACK(b, CHOOSEROWS(d, XMATCH(c, SEQUENCE(ROWS(d)))))))

=LET( a, Sheet1!K4:K186, b, TAKE(a,,-1), c, SCAN(0,b,LAMBDA(x,y,IF(y<>0,x,x+1)))+1, d, Sheet2!C7:F36, IF(b=0, "", IF(b="", "", HSTACK(b, CHOOSEROWS(d, XMATCH(c, SEQUENCE(ROWS(d))))))) )

=IF(NOT(ISBLANK(G4)), G4, IF(NOT(ISBLANK(E4)), E4, IF(NOT(ISBLANK(C4)), C4, "")))

=IF(NOT(ISBLANK(G4:G196)), G4:G196, IF(NOT(ISBLANK(E4:E196)), E4:E196, IF(NOT(ISBLANK(C4:C196)), C4:C196, "")))

=VLOOKUP(H4, Roles!$B$30:Roles!$C$54, 2, FALSE)

=IF(H4<>"", VLOOKUP(H4, Roles!$B$30:Roles!$C$54, 2, FALSE), "")

=IF(H4:H196<>"", FILTER(Roles!$C$30:$C$54, Roles!$B$30:$B$54=H4:H196), "")

=IF(H4<>"", VLOOKUP(H4, Roles!$B$30:$C$54, 2, FALSE), "")

=IF(C7:C202=0,"","INSERT INTO SOME_TABLE (COL1,COL2) values('"&C7:C202&"','"&D7:D202&"');")

=IF(OR(C7:C202=0, ISBLANK(C7:C202)), "", "INSERT INTO SOME_TABLE (COL1,COL2) values('"&C7:C202&"','"&D7:D202&"');")

=IF(OR(ISNUMBER(FIND(CHAR(10), A1)), ISNUMBER(FIND(CHAR(13), A1))), "Contains CR or LF", "Does not contain CR or LF")

=IF(C7:C202=0,"","INSERT") =IF(C31="","empty cell",IF(C31=0,"Zero Cell","Cell > Zero"))

=IF(C7:C202="", "", IF(C7:C202=0, "", "INSERT"))

=LET( range1, Sheet1!A1:A20, range2, Sheet2!B5:B25, range3, Sheet3!C10:C30, combined, VSTACK(range1, range2, range3), INDEX(combined, SEQUENCE(ROWS(combined))) )


Solution

  • Here is one way of doing the same:

    enter image description here


    =LET(
         a, Sheet1!D6:E28,
         b, TAKE(a,,-1),
         c, SCAN(0,b,LAMBDA(x,y,IF(y<>"",x,x+1)))+1,
         d, Sheet3!D4:E6,
         IF(b="","",HSTACK(b, CHOOSEROWS(d, XMATCH(c, SEQUENCE(ROWS(d)))))))
    

    Explanations: