As seen below, I have an excel file where collections from debtors is recorded.
Column A is the staff code who made the collection..
Column B is the date of collection
Rest is self explanatory
Out of this table, on a different sheet, I need to extract data from this table and populate a new table with the filtered data. E.g., On the second sheet, I need a table to show collections from only P102, of which I have managed to do with the below array formula:
{=INDEX(Sheet2!$A:$A,SMALL(IF(Sheet2!$A:$A="P102",ROW(INDIRECT("1:"&COUNTA(Sheet2!A:A)))),ROWS(Sheet2!$A$2:$A2)))}
Of course this code outputs only the Personnel code but I've ammended this to show for other columns too..
The thing I can't manage to do is, I also need to apply a second filter with the date.. Eg. Only those records from P101 and only in February..
I would really appreciate any feedback on this…
P.S. I do not wish to use Macros and No, I can't make a pivot table…
Regards,
Kemal
Best Answer
I would use the Power Query Add-In for this. You can specify a list of Filters, to work in combination. The filtering functionality is very good.
http://office.microsoft.com/en-ca/excel-help/filter-a-table-HA103993866.aspx#_Toc354835496