Excel – Populate Excel table based on conditional options from another

microsoft excelmicrosoft-excel-2013

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

enter image description here

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

Related Question