Filtering Report

[ruchi1127]

Hi I have a report with over 15,000 rows by entities and types of holdings within each company. The report also has titles lines and subtotal lines. I am concerned in filtering through the data and only picking up certain information. I want to only pick up the highlighted (green) information and ensure that everything is being picked up accurately without missing data. I do not want to pick up the lines in between or the subtotal lines. What is the most efficient way? First I thought of linking it to a new worksheet but that was very manual and can easily miss a few rows.

[newdoverman]

Welcome to the forum.

I hope that this is a report generated from actual data and not the actual way that data is entered as this layout of data is quite difficult to deal with.

Are each of the green areas growing or are they static and how many green areas do you have?

Also, what do you expect the result to look like?

I am enclosing your workbook. I copied the data to a second worksheet, selected the data and inserted a table with a TOTAL row at the bottom. I then on the first column deselected Blanks, Investment Grade, and Short Terms to give a table with the columns added correctly. (I added dummy figures in some columns to test)

This is just to give you an idea. You may have something entirely different in mind.

[ruchi1127]

Thanks for replying to my inquiry. Can you please walk me step by step as to what you did so I can try to see if it works on all the 15k rows I have in the file.

This was just an example of how my file looked and I deleted the data and the file had over 15000 rows so I did not attach the entire file. The green area does not change frequently but may from month to month. Also the report is split by different entities, so it’s showing current holding by each entity. I am trying to find a better way of just getting the holding and subtotal which I can use to link to another file instead of trying to separate the rows manually.

[newdoverman]

1. I selected all the data stating at B6 so the column headers are included.
2. I copied this selection to Sheet2 and pasted into C3.
3. While still selected, I clicked on Insert Table making sure that My Table Has Headers is selected.
4. On the Design Tab I selected Total Row. This adds a row at the bottom of the table that only totals visible rows.
5. I clicked on the Filter button on the first column and deselected Blanks, Investment Grade and Short Terms and sorted from A-Z
6. In the Totals Row, I clicked on each column that holds values and from the drop down selection chose SUM. This was repeated for all the columns to be totaled.

The totals for the visible rows is automatically summed for you.

To get a new report after added or removed, redo the above.

OR

You can select the data on the first worksheet as above and Insert Table instead of copying. This will allow you to work on the first worksheet adding and removing data as required. On the design tab, uncheck Banded Rows and check Total Row. In the Styles section of the Design Tab scroll to the top of the listings and take the leftmost selection. This will get rid of the obvious table formatting leaving your data looking pretty much like it started out. You can even go to the Data tab and click on Filter and the filter buttons will disappear and when you need them, click on Filter and they will be back for you to use.

I have created a table on Sheet1 as described and the filters have been turned off. Go to the Data tab and click on Filter and the filter buttons will appear.

Sheet2 is the copy version.