Forum Discussion
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, multiple invoices listed in each category)
Column Q - Invoice date
Column AA - Supplier
Column AB - Invoice Description
Column AC - Amount of invoice
Column AD - Amount claimed
I had used the Filter formula, but this insists on listing all the columns, even the non relevant ones, and
A pivot table won't let me show
Date, Supplier, Description = Amount
on the same line (because adding new criteria groups the table by those criteria).
Lookup functions seem to rely on my data being in specific rows or columns (which it is not) and I want to produce a whole table, without using a Pivot which requires a new filter or Query each time. Is there any way to get the Filter formula to ignore the rest of the table (ie to not "spill") or can anyone suggest a way of achieving a new table (without queries, but which will update from my original data)? Should I try doing lots of IFS, then Concatenate, for example (I figured there should be an easier way)?
Many thanks in advance.
9 Replies
This is doable with FILTER. What are the names of the columns you'd like to exclude?
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?
Can you please list the columns you want to keep? Thanks.
Abiola1I have cleaned down the spreadsheet somewhat as it is quite large, but hopefully there is enough left on the attached to demonstrate what I am trying to achieve on the draft Schedule of claim, where I am just wanting to extract and reproduce some of the entries but not all - sort of a pivot table (layed out in columns) hybrid with a filter (extracting only the relevant entries). What do you think?
