Forum Discussion
execl joining columns
how do you link the 2 columns together so that 678 is followed by 123 in a line downwards ?
=TOCOL(range, , 1)
Maybe you do not have Excel 365? With old Excel, a starting point might be
= INDEX( array, 1+MOD({1;2;3;4;5;6}-1,3), 1+QUOTIENT({1;2;3;4;5;6}-1,3) )but either it would need to be committed with Ctrl/Shift/Enter or placed within a defined name, where array formulas have always worked correctly. I am glad to say I no longer need to use these horrible old systems!
- Peter
what book would you recommend to learn the excel 2121 functions?There must be books. I remember their being an early one on dynamic arrays by Bill Jelen. I would also look at the Microsoft help pages on LET and LAMBDA. TOCOL and other array shaping functions such as WRAPROWS came later. There are also many posts to be found from Microsoft MVPs.
If you want to read about recent changes to the art of the spreadsheet from my personal viewpoint you could take a look at a paper I gave to EuSpRIG
[2309.00115] Excel as a Turing-complete Functional Programming Environment (arxiv.org)
Note: All the later changes require 365. Basic array functions like FILTER were included in Excel 2021.
- Peter
thank you for your reply.
iam using office pro 2021, but a newbe to excel functions do you recommend excel 365
