#CALC! in VSTACK formula with embedded FILTER, NOT and COUNTIF

[judocary]

I am using Excel in Microsoft 365 for business. The formula I am using is =VSTACK(FILTER(A4:A99,NOT(COUNTIF(L4:L107,A4:A99))),FILTER(L4:L107,NOT(COUNTIF(A4:A99,L4:L107))))

This formula compares a list of alphanumeric cells in column A to similar data in column L and spits out what is in A but not L and what is in L but not A. Problem I'm having is the actual data in column A is from cell A4 to A98. I had to use A99 because otherwise I get a #CALC! error if the range is A4:A98. Very strange. This formula used to work about two months ago, but now it doesn't work unless I use an extra cell for column A or one less cell for column L (e.g. L4:L106). However cell A99 is an empty cell so i get a result ("0") that I do not need. This is my work around for now but it is not ideal.

I also tried using table names instead and still get the same #CALC! error using range A4:A98. The error goes away if the table range includes cell A99.

I also tried using ranges that are larger than I need (e.g. A4:A107) which works but I get results that include "0" for all the extra blank cells from A99 to A107. I even shortened the range one cell at a time (A107, A106, A105, A104...) and it works until I get to A98 which creates the #CALC! error.

[Gregb11]

Please read the yellow banner at the top of this page to attach a good sample workbook.

[judocary]

file attached. thanks for the heads up. A coworker suggested using this formula instead which works =UNIQUE(VSTACK($A$4:$A$98,$L$4:$L$107),,TRUE)

[AliGW]

The crux of the problem, I suspect, is that your COUNTIF ranges are not the same dimensions (number of rows). Provide a sample workbook as advised in post #2.

[judocary]

The crux of the problem, I suspect, is that your COUNTIF ranges are not the same dimensions (number of rows). Provide a sample workbook as advised in post #2.

Thanks! File attached in original post.

[AliGW]

So is this resolved, then?

[judocary]

The new formula works but I am still curious as to why the original formula stopped working just a couple of months ago after no issues in the past.

[judocary]

Yes with the new formula. but I'm curious as to why the original formula stopped working a couple of months ago. very strange.

[AliGW]

I really couldn't say - I haven't seen it working, but I can tell you, now I've seen the solution your colleague came up with, that it was over-engineered. I would confine it to the dustbin of experience and move on with your new and streamlined alternative. It's not worth debugging it.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

[AliGW]

As I say - nobody here can answer this having never seen it working. Formulae do not 'just stop working' - something must have changed somewhere, but we cannot deduce what, as we can't see into the past, so I'm sorry, but it's a non-starter.

[Gregb11]

And I would say it's what Ali said in Post #3 - the ranges were different. When your filtering a range and you're comparing it to a range of a different size, it can't filter that.

Let's say range 1 was 2 records - R1 and R2 and range 2 was 3 records X1, X2, and X3.

when you FILTER R1 and R2 to see if X1:X3 are there you have a mismatch. The "INCLUDE" part of the filter will come back with TRUE, FALSE, TRUE (or any combination). So it can't filter 2 rows based off of 3 conditions. (hard for me to explain but hopefully that makes sense.)