Search code examples
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:

    • Using LET() function it helps to define variables and reduce redundant calculations.
    • Variable a is defines the data from Sheet1 --> Sheet1!D6:E28
    • Variable b is defines the second column range of data taken from a using TAKE() function, so using TAKE(a,,-1) returns the specific range.
    • Next, Variable c uses a LAMBDA() helper function called SCAN() which helps in returning an array with the help of the custom formula applied to the LAMBDA() which iterates through each value in the array and returns an array of outputs for each intermediate element in the array. So, it checks for the b if the range is not empty and fills the value from above, its like we do usually using the fill values from above same way but using dynamically !
    • Next, the variable d refers to the data from Sheet3
    • Finally using a simple XMATCH() to match the numbers of rows in sheet 3 data with the variable c and using CHOOSEROWS() to fill the respective data and lastly to keep and maintain the formatting of blanks we are using IF() to parse those out.