On the Data worksheet, data for payments received from different customers is shown invoice wise. Since payments are received in tranches, they are shown in different columns – column C and D show dates and amounts received in the first tranche respectively and column E and F show dates and amounts received in the second tranche respectively.
One may want to generate the list of Invoice Number, Customer Name and the Amount for a particular date. Since dates are split across two columns, it will not be possible to filter the dataset. In order to filter the dataset, one will have to append data from alternate columns.
I have been able to accomplish this by using Power Query – a free add-in from Microsoft for Excel 2010 and higher versions.
You may refer to my solution in this workbook.
You may watch a short video of my solution here