Jul 16 2021 02:37 AM
Hello everybody!
Hope you're great. I have a problem: I am quite new in using Excel, and I am trying to solve it but without useful results.
I have a large list of numbers (say e.g. 50) in one column and once I fix a rate number, I want to generate different rows with the concatenation of a rate number of the elements in the first column.
For example, if in Column A I have number from 1 to 50 and my rate is 10, i want cell (say) B1 to have the following string: "1; 2; 3; 4; 5; 6; 7; 8; 9; 10; ". Then cell B2 to have: "11; 12; 13; 14; 15; 16; 17; 18; 19; 20; " and so on, until cell B5 to have: " 41; 42; 43; 44; 45; 46; 47; 48; 49; 50". I would like to make this process authomaticly using some combination of formulae, but still cannot manage to find the right one. I attach the file in which I am making my tries.
If you know how to solve this and help me, I'll be extremely grateful to you. :)
Jul 16 2021 08:00 PM
If you are open to a VBA solution, please find the attached which contains Sheet Change Code on Foglio 2 Sheet Module and a macro on Module1.
On Foglio 2 Sheet, if you change the rate in cell T2 (yellow cell), you will get the desired output in column B.
Jul 18 2021 11:50 PM
Jul 19 2021 12:44 AM
SolutionHi @LM_95
One way assuming your version of Excel (you did not mention it as asked in Welcome to your Excel discussion space!) has function TEXTJOIN
in B4 and copy down as necessary:
=TEXTJOIN(", ",TRUE,OFFSET(A$4,(ROW()-ROW(A$4))*B$1,,B$1))
Your file with the above formula is attached
Jul 19 2021 01:18 AM
Jul 19 2021 02:00 AM
Jul 19 2021 12:44 AM
SolutionHi @LM_95
One way assuming your version of Excel (you did not mention it as asked in Welcome to your Excel discussion space!) has function TEXTJOIN
in B4 and copy down as necessary:
=TEXTJOIN(", ",TRUE,OFFSET(A$4,(ROW()-ROW(A$4))*B$1,,B$1))
Your file with the above formula is attached