Forum Discussion
Filter selected columns in a specific order
Hi All,
If my source data contains 6 columns:
A B C D E F
and I want to FILTER 3 columns with the order being
A F D (i.e. not in the same sequence as the source)
It does seem to work.
I am suing the formula
FILTER(Source, Countif(HeaderRow,SourceHeaderRow))
where SourceHeaderRow is ABCDEF
and Header row is AFD
In other words, it seems to me that the FIltered data must be in the same sequence as the source data.
Can this be done.
Please note that I do not have access to CHOOSECOLUMN function.
I hope this makes sense!
thank you in anticipation.
Peter
Hi
Many thanks for your input - I am trying to get it to work but no luck so far.
I think this is because I do not understand how Sequence evaluates the curly brackets i.e. {1,6,4}
As I understand it the second parameter in Sequence is COLUMNS - not sure how it evaluates {1,6,4}
Can you advise please?
thank you!😀
5 Replies
Hi peteryac60​,
If you have access to the SEQUENCE and ROWS functions, try this formula:
=INDEX(A1:F10, SEQUENCE(ROWS(A1:F10)), {1,6,4})Note: Adjust the range in the formula according to your needs.
HTH
IlirU
Hi
Many thanks for your input - I am trying to get it to work but no luck so far.
I think this is because I do not understand how Sequence evaluates the curly brackets i.e. {1,6,4}
As I understand it the second parameter in Sequence is COLUMNS - not sure how it evaluates {1,6,4}
Can you advise please?
thank you!😀
From what I can see, your reply has already been marked as the answer, which means you've probably found the solution you were looking for. However, I am still sharing my explanation for the question you asked.
The formula I provided is:
=INDEX(A1:F10, SEQUENCE(ROWS(A1:F10)), {1,6,4})
Meanwhile, just to clarify, the syntax of the INDEX function is:
=INDEX(array, row_num, [column_num])
So, {1,6,4} in my formula represents the column numbers in the exact order you want to extract your data from the table A1:F10.
Using curly brackets tells Excel the specific order it should use for the columns: first column 1, then column 6, and finally column 4.
This way of listing the columns makes Excel rearrange them according to the criteria you set. For example, if you used {2,5,1,3}, Excel would return the second column, then the fifth, followed by the first, and finally the third column.
I hope this answers your question and helps you understand how column numbers work within the INDEX formula.
Regards,
IlirU
