Thread: [Solved] sorting cells in excel into specific columns
View Single Post
 
Old 05-10-2022, 01:27 AM
p45cal's Avatar
p45cal p45cal is online now Windows 10 Office 2019
Expert
  
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
For just those two columns, and if you have O365 with the LET and SEQUENCE functions, then in any single cell:
Code:
=LET(rng,Sheet1!A1:A100,INDEX(INDEX(rng,SEQUENCE(ROWS(rng)/4,4)),SEQUENCE(ROWS(rng)/4),{1,3}))
and if this is on the same sheet there's no need to refer to the sheet name:
Code:
=LET(rng,A1:A100,INDEX(INDEX(rng,SEQUENCE(ROWS(rng)/4,4)),SEQUENCE(ROWS(rng)/4),{1,3}))
These formulae will spill down and across so make sure there's nothing in those cells else you'll get #SPILL! in the cell with the formula.
Reply With Quote