Forum Discussion

JenGeneve's avatar
JenGeneve
Copper Contributor
Jan 13, 2020

Filter function

I am trying to extract data for a Schedule of Claim, from a data sheet which goes up to cell BS1233 (so quite large).  The data inclues items organised into, eg: Column K - category of claim (A-K...
excel
Formulas and Functions
JenGeneve's avatar
JenGeneve
Copper Contributor
Jan 14, 2020

Patrick2788  Thanks for your response, really I want to exclude most of the columns, and just filter positively to show the columns I want - I have uploaded a file - do you know if this is possible?

 

Patrick2788's avatar
Patrick2788
Silver Contributor
Jan 14, 2020

JenGeneve 

Can you please list the columns you want to keep? Thanks.

  • JenGeneve's avatar
    JenGeneve
    Copper Contributor
    Jan 14, 2020

    Patrick2788 

    P, then D, then R, then I - is it possible to show them in that order?

     

     

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jan 14, 2020

      JenGeneve 

       

      I think you can run with this. This is the idea:

       

      My sample data is in columns A to R

       

      =FILTER($A$2:$R$5,(COLUMN(A1:R1)=16)+(COLUMN(A1:R1)=4)+(COLUMN(A1:R1)=18)+(COLUMN(A1:R1)=9))

       

      Excel will re-order the columns pulled back (e.g. 4, 9, 16, 18). It ignores my ordering.

      • JenGeneve's avatar
        JenGeneve
        Copper Contributor
        Jan 15, 2020

        Patrick2788 

        Thank you for taking an interest in this problem. I have had another look,  and tried to adapt your proposed solution so that it would filter out rows based on containing the relevant "Head of Claim" reference from column H, and also to put the columns in the right order - it is so bizarre that it ignores the results order your formula below prescribes!   If Excel won't do it automatically, I guess I will just have to keep doing it long-hand.