Find & Export: Is it possible? - Attached file now

I have a worksheet or three full of names in one column, expenses & explanation in the next ones. They are grouped by month.

Is it possible to have excel search/find a specific name and pull all occurrences for the full year, along with the data, and then export it to another worksheet?

I suppose I could just arrange alphabetically, then manually copy/paste to another page, trying to automate it and look smart(er).

see big yellow banner - how to upload workbook.
far easier to demonstrate on an actual workbook - the answer is yes it can be done.

Are you still running Excel Version 2007? If not, please update your profile as this will make a significant difference in what solution is offered. The more current your version, the more options available.

The attached file has a small macro on the Worksheet_SelectionChange event.
In column 'A' click in the blank cell adjacent to persons name - name appears in cell 'B1' - this triggers the code to filter/copy relative entries to second sheet.
A far more elegant approach would be to use a single table with an extra 'date' column - filtering then becomes a simple task.

FYI: Since you are still on 2007, I cannot offer you a solution. I had a Power Query solution for you but you will need to be on 2010 or later for that to work. Good Luck.

I'll take that solution if you don't mind, I can apply it to newer excel version.

It would be helpful to let us know at the outset which version(s) of Excel the solution will be used on. What will that newer version be?

Here is the PQ solution attached.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

One of us will talk you through it. I can't now as I have to go out shortly, but if Alan hasn't when I get back, I'll explain it.

1. Bring the first table into PQ Editor In 365 its, Data, Get and Transform, From Table/Range
2. System automatically changes data type
3. In editor, Filter out null rows in Column 1
4. Remove all columns except 1, 3 and 5. Highlihgt those columns, select remove columns and then remove others
5. Add a custom column that grabs column 1 if that column contains either January or February, otherwise place a null in the new column
6. In the new column select Fill Down. PQ will replace all the nulls with either January or February
7. Filter out the nulls in column 5
8. Move the Month Column all the way to the left.
9. Promote Row 1 to become your field names (headers)
10. Rename column 1 to "Month"
11. Create a new table containing the information in cells H1:H2
12. Once in the PQ editor, right click on the value in row one and Drill Down.
13. Close and load the new query to Connections
14. In the claimant column filter for any value.
15. Open the Mcode, Home Tab, Advance Editor
16. Manually change the filtered value for the name of the new table.
17. Close and load the original table to Excel.

Any changes to the Claimant Table and then select in 365 on the Data tab, the refresh button.

Look at this link for a tutorial on building parameter queries.

https://www.youtube.com/watch?v=gK2yBpiITvI

Alternative - UserForm for data entry - if name or expense type not already listed then after you have typed it in once it will thereafter become available from drop-down.
The right-hand side of form allows you to filter between dates for individuals - passing this onto USB for storage or to PDF for print out.
The VBA is deliberately written longhand to make it more readable so that you can follow the code.
The data is stored in one table - there are no on-sheet formula to bother about - depending on your Excel version you could store over 1 million rows.

Please update your profile.