SOLVED

Concatenate elements via formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2555399%22%20slang%3D%22en-US%22%3EConcatenate%20elements%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555399%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everybody!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you're%20great.%20I%20have%20a%20problem%3A%20I%20am%20quite%20new%20in%20using%20Excel%2C%20and%20I%20am%20trying%20to%20solve%20it%20but%20without%20useful%20results.%3C%2FP%3E%3CP%3EI%20have%20a%20large%20list%20of%20numbers%20(say%20e.g.%2050)%20in%20one%20column%20and%20once%20I%20fix%20a%20rate%20number%2C%20I%20want%20to%20generate%20different%20rows%20with%20the%20concatenation%20of%20a%20rate%20number%20of%20the%20elements%20in%20the%20first%20column.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20in%20Column%20A%20I%20have%20number%20from%201%20to%2050%20and%20my%20rate%20is%2010%2C%20i%20want%20cell%20(say)%20B1%20to%20have%20the%20following%20string%3A%20%221%3B%202%3B%203%3B%204%3B%205%3B%206%3B%207%3B%208%3B%209%3B%2010%3B%20%22.%20Then%20cell%20B2%20to%20have%3A%20%2211%3B%2012%3B%2013%3B%2014%3B%2015%3B%2016%3B%2017%3B%2018%3B%2019%3B%2020%3B%20%22%20and%20so%20on%2C%20until%20cell%20B5%20to%20have%3A%20%22%2041%3B%2042%3B%2043%3B%2044%3B%2045%3B%2046%3B%2047%3B%2048%3B%2049%3B%2050%22.%20I%20would%20like%20to%20make%20this%20process%20authomaticly%20using%20some%20combination%20of%20formulae%2C%20but%20still%20cannot%20manage%20to%20find%20the%20right%20one.%20I%20attach%20the%20file%20in%20which%20I%20am%20making%20my%20tries.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20know%20how%20to%20solve%20this%20and%20help%20me%2C%20I'll%20be%20extremely%20grateful%20to%20you.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2555399%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2558045%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenate%20elements%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2558045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105155%22%20target%3D%22_blank%22%3E%40LM_95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20open%20to%20a%20VBA%20solution%2C%20please%20find%20the%20attached%20which%20contains%20Sheet%20Change%20Code%20on%20Foglio%202%20Sheet%20Module%20and%20a%20macro%20on%20Module1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Foglio%202%20Sheet%2C%20if%20you%20change%20the%20rate%20in%20cell%20T2%20(yellow%20cell)%2C%20you%20will%20get%20the%20desired%20output%20in%20column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2561129%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenate%20elements%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2561129%22%20slang%3D%22en-US%22%3EDear%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%20for%20your%20answer%2C%20I%20really%20appreciate%20it.%3CBR%20%2F%3EUnfortunately%2C%20I%20am%20not%20used%20to%20VBA%20and%20macros%2C%20so%20is%20there%20a%20way%20I%20can%20do%20it%20via%20%22basic%22%20formulas%20without%20VBAs%20nor%20macros%3F%3C%2FLINGO-BODY%3E
New Contributor

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.  

5 Replies

@LM_95 

 

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.

 

 

Dear @Subodh_Tiwari_sktneer,

Thank you very much for your answer, I really appreciate it.
Unfortunately, I am not used to VBA and macros, so is there a way I can do it via "basic" formulas without VBAs nor macros?
best response confirmed by LM_95 (New Contributor)
Solution

Hi @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

 

Demo.png

 

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

This is exactly what I was looking for. Thank you very much!
You're welcome. Thanks for posting back & nice day...