Compatibility error on older versions -Unique formula

[Zed69]

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! :crossed_fingers:

[#2]

@Zed69 UNIQUE is supported by the following versions.

Excel 2019 is NOT one of them.

[#3]

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

[#4]

@Zed69 My apologies! I misread your initial post and thought you were looking for a way to get the UNIQUE function working in older version.

Google for "alternative unique excel" and you'll find various sources that explain how to do it. You'll use a combination of IFERROR, INDEX, MATCH and COUNTIF, 

[#5]

@Zed69 Or perhaps a few pivot tables could help generate the unique lists for you. See attached.

[#6]

@Zed69 

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?

[#7]

@JMB17 Good point on PQ. Added it to your file. Not at all that difficult.

[#8]

Thank you. I thought it could do it, but wasn't entirely sure if there would be any compatibility issues. Seems to work fine on my machine, which is 2016, so appears to be an option.

Also, I noticed a slight error in one of the example formulas where an empty cell in the table generates an error. Corrected:
=INDEX(Orders[Item],SMALL(IF(MATCH(Orders[Item]&"",Orders[Item]&"",0)=ROW(Orders[Item])-ROW(Orders[[#Headers],[Item]]),ROW(Orders[Item])-ROW(Orders[[#Headers],[Item]]), ""), ROWS(F$2:F2)))

But, for the OP, I would look at the pivot table/power query first - I think those would be much easier to maintain and update than a formula that doesn't spill.