Forum Discussion
Filter, Sortby...help with formula needed!!
I wish to create a table on Sheet 2 extracted from a table on Sheet 1. I only wish certain columns to be used, driven by whether Col 5 is populated. These will then need to be columnised into a different order and then sorted by e.g. Col 5.
Is there a formula that i can use?
Yes. you can use a combination of CHOOSECOLS and FILTER and SORT
let assume your original Table is called DataTable, then maybe:
=SORT(FILTER(CHOOSECOLS(DataTable, 5,3,1),CHOOSECOLS(DataTable, 5)<>""))
I may need to see more sample data but you could use GROUPBY to do the filtering and sorting.
=LET( cols, CHOOSECOLS(DemoTbl, 5, 3, 1), rpt_filter, DemoTbl[Col 5] <> "", GROUPBY(cols, , , , 0, , rpt_filter) )Thanks for your help, but i couldn't get this one to work 😞
Yes. you can use a combination of CHOOSECOLS and FILTER and SORT
let assume your original Table is called DataTable, then maybe:
=SORT(FILTER(CHOOSECOLS(DataTable, 5,3,1),CHOOSECOLS(DataTable, 5)<>""))
This worked a treat, thank you 👍
Thanks, I’ll try it when I’m back at work on Monday.
select * from Sheet1 limit 20;
select f05,f03,f01 from Sheet1 where f05 not like '' order by f05;
