Get calculations to spill along with the FILTERed or UNIQUEd data

Hello all

I have 2 sheets of data out of which I'm trying to create several tables. Problem is, while I can create a source data for tables with FILTER & UNIQUE, I can't seem to get the calculations to spill automatically to match the filtered and uniqued data.

It could be better visualized in the attached sample. Calculations in B2, D2, F2 and N2 are what I'm trying to spill automatically. Could this even be done?

PS: The original data is quite large and ever growing, so I'd prefer to stick to column references for range references in the formulas if/whenever possible.


I don't have to use the functions I did in the manually calculated columns, as a rule. If there are other functions that would get the calculations to spill, or other methods that gives the same results, I'm open to using them.

Also, while not an extreme necessity, if there is a way to remove the 0 at the bottom of column A that would be great. I understand why it's there, but it's visually really disturbing me But as far as I could tell it's not disrupting the formulas so if it's going to complicate things it's not very important.

Best regards.

Dee3.JPG

Hello TMS

Thanks for quick response!

A1 and D2 worked but F2 formula seemed to ignore IF.

Also, I believe N2 and B2 cannot be spilled the same way?

I'm not familiar with DNR. How would it work? Please note the original data in "Other Sheet" is about 550.000 rows (A2:J553771 to be exact, and will continue to grow), so how well would a DNR perform with such data?

And how would you recommend me to proceed?

Hello TMS

Just got to the office and I will try to get it work on the main data. In any case, you've been amazingly helpful!

Many thanks!

Hello TMS

After trying endless functions, I finally got it to work. (for N column)

=IFERROR(IFS(FILTER(M:M,ISNUMBER(M:M))=84,"SOLD",FILTER(M:M,ISNUMBER(M:M))=95,"SOLD"),"KEEP")

I'll be trying something similar for B, I'll let you know if it works!