FILTER Function - How to condense a worksheet after filtering

Hello,
I have 3 different data sets/tables on the same worksheet that are being dynamically filtered using the FILTER function that allows you to search and filter by ANY field/word. However, once I filter the results then I'd like to have the data sets shift/move up so that there aren't huge gaps between each data set after they've been filtered. Because otherewise you have to scroll down quite a bit until you reach the next filtered data set. For example, using the example file that's attached if I wanted to insert the word "Goods" into the filter, it will filter all 3 data sets but I'm also needing the data sets to condense/move-up so that there aren't huge blank gaps between each data set.
Condensing Filtered Data Sets.xlsx

Please read the yellow banner (top) and post a SMALL sample file (10-20 rows) showing what you want.

Before tackling your actual problem... please see if this replaces your monster formula...


=IFERROR(FILTER(FTPRate,BYROW(--ISNUMBER(SEARCH($H$3,FTPRate)),LAMBDA(x,SUM(x)))>0),"No results")

I sort-of wonder why you want every field to be searchable.. if the raw data is any cell is 1234.56, it is rounded to 1235. That is what you SEE. If you search for what you SEE, it will search for 1235 and NOT find 1234.56. Is that really of any use?? This is the point DJunqueira was making...

Modified, as specified.

EVERYTHING from row 10 downwards MUST be deleted.

It now does what you asked for.

One issue remains. There is (always was) a total row at row 8. Do you need a substotal row for each dataset? Is it OK to have it on rows 10, 20 and 30 (when mortgage is selected)? If not, where?

My take on what you have asked for... See file. The summary data for the first two columns have been done for all 3 datasets. I have hit a snag. The formulae in AA to AF have stopped working, so I have no way of checking if the calculation is correct. Can you restore them using ranges appropriate for the FIRST dataset when MORTGAGE is selected. Eventually row 8 will be deleted.

Thanks Glenn, I've restored the data in columns AC:AF

Condensing Filtered Data SetsV3.xlsx

More issues, some arising from earlier unanswered issues

1. The seach term in D5. Can it be ONLY those terms like "Mortgage", Money market", etc. as opposed to searching for amounts?

2. Those summary formulae are a bit of a headache. I CAN do them, but it is NOT going to be easy, or quick!!

3. I'm tempted to start over again, but need an answer to Q1...

One the verge of giving up.

You seem to have gone back to square -20. Everthing I have done so far is in the bin.

Take a look at the ANSWERS generated by your formulae in columns Z to AE and explain HOW they were derived.

Are they giving the correct results?

Tell me WHICH row is being returned in each column..

NO. They are not.

Take the formula in AB10.

1. It is looking up the value in B10 (Mortgage) in the range '1. PopulationAttribution'!$S$613:$S$760. First mistake, You have NOT specified an exact match in the formula, so Excel is doing its own thing.

2. It finds a match on row 60 of the range. There are no fewer than 12 rows in that range that correspeond to mortgage (highlighted in orange). How does Excel know which ONE to choose? Second mistake. You have not told it.

3. If you use evaluate formula in Excel tools, you will see that the formula eventaully decides that it matches on row 60 of the range '1. PopulationAttribution'!$V$613:$V$760. The value on row 60 is, of course 0.055816. I have highlighted to row in purple. If you look at column S of that row, it refers to Acquired loans, NOT mortgages. If uou don't believe me... change cell V672 to 20. Look at column AB again.

So. The calculated values are simply WRONG.

As I said, there are 12 values for mortgage in that row. WHICH one is correct. it is not row 60.

I could not reproduce your answers in your total rows when I started to look at the formula in (one of) your previous files... when the Pop. Attrib. tables had only ONE entry for mortgage. Hence my previous attempts to get you to look in detail at your own formulae. Now there are 12 possible matches, no indication of which one is correct, and the one it does return is for an entirely different category.

So. Read the above carefully and respond even more carefully.

By now, I would normally have walked away from a problem like this. Such a major redefinition of a fairly massive project!!

HOWEVER...

Take a look. If there any further major changes, I'll be hiring a hit man. Capiche?

Thank heavens!! I'm in a remote village in the centre of Fuerteventura right now. It's been threatening to pour all day so it gave me something to do... in between reading and taking a few language lessons on Duolingo.

You're welcome and thanks for the feedback.

NM i was able to update the code accordingly