I want to copy the values but just only the higher than one (>1) from column $AE$3:$AE which is I'd given the name with "MANUAL INPUT OF NUMB" and want to set those values onto a column $AG$3:$AG then repeat them depends on how many the duplicate names are in a column $R$3:$R which is I'd given the column the name with "N A M E". Can we make this happend with formula or arrayformula ? or Chould we do this by any others way as an alternative ways if the formula doesn't work or can't do this doing such a like this thing or custom formulas something like that ?

And I've tried with my tests formulas : =INDEX(QUERY(SPLIT(FLATTEN(SPLIT(REPT("‍"&FLATTEN(QUERY(TRANSPOSE(IF($AE$3:$AE>1;$AE$3:$AE&"​";""));"";""));$AE$3:$AE);"‍"));"​");"where Col1 is not null";1)) after doing some research on column $AG$3:$AG and it's failed it doesn't work at all as I want it as I imagine it. Please see my pictures that I've attached down below these and see also the table I've created for more further explanation.

CodePudding user response：

use:

=INDEX(LAMBDA(c; IF(c>1; c; ))(IF(A2:A="";; 
 VLOOKUP(ROW(A2:A); IF(B2:B<>""; {ROW(A2:A)\ B2:B}); 2))))

update:

=INDEX(LAMBDA(z, y, IF(y, z, ))(LAMBDA(x, IF(x>1,x,))(IF(A3:A="",,VLOOKUP(ROW(R3:R),IF(B3:B<>"",{ROW(R3:R),B3:B}),2))), ""<>IF(VLOOKUP(ROW(R3:R),IF(IF(INDIRECT("A2:A"&ROWS(A:A)-1)<>A3:A, row(A3:A), )<>"",{ROW(R3:R),IF(INDIRECT("A2:A"&ROWS(A:A)-1)<>A3:A, row(A3:A), )}),2)=IFNA(VLOOKUP(ROW(R3:R),IF((IF(INDIRECT("A2:A"&ROWS(A:A)-1)<>A3:A, row(A3:A), )<>"")*(B3:B>1),{ROW(R3:R),IF(INDIRECT("A2:A"&ROWS(A:A)-1)<>A3:A, row(A3:A), )}),2)), VLOOKUP(ROW(R3:R),IF(IF(INDIRECT("A2:A"&ROWS(A:A)-1)<>A3:A, row(A3:A), )<>"",{ROW(R3:R),IF(INDIRECT("A2:A"&ROWS(A:A)-1)<>A3:A, row(A3:A), )}),2), )))