Forum Discussion
Compatibility error on older versions -Unique formula
Hi Any help at all please?
I have 2 computer with Microsoft Office. One has Business 2019 (at home) and the other is 365 (at work) On the new one I created an XL containing some unique formulas on Materials, picked from but from the Orders tab, however when I emailed it to myself and opened it on the newer one it messed the formulas and took me over a week to realize that they are not compatible!
I need to be able to use this on both machines. I've tried saving the XL in other versions like 97-2003,2010,2013 but there are so may incompatible errors flagging up it doesn't work, and then also it wont update my workbook to the latest version. I'm after either some help on a formula that will work on both versions. The current formula on the 365 works perfectly =
=UNIQUE(Orders!C2:C534) however on downloading to the other computer
{=UNIQUE(Orders!C2:C534)} or
=_xlfn.UNIQUE(Orders!C2:C534)
If I could have the same version on both, their wouldn't be an issue, however I can't do this, plus it will used by possible another locations, so it needs to suit all. I cannot imagine that anyone has an older version that 2019, so as long as it works on that or higher, then that's great.
Thank you! 🤞
7 Replies
- Yep I as mentioned, I realized that a few days ago, but isn't there another formula that is compatible with both versions that can give me the same result please? I basically want to compile a list of items on the 2nd tab ignoring any duplicates, thanks
Two formula examples attached. But, I don't think XL 2019 has dynamic array, so the formulas won't spill the results. Therefore, you have to copy them down the column until you get an error (or, use Iferror to display something different). I also converted Orders to use a structured table (as the table size changes, the formula range references don't need changed).
I don't know enough about power query to say for sure it's possible, but you might look around to see if this is something it could do?
