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 ...
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?
JMB17 Good point on PQ. Added it to your file. Not at all that difficult.
- 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.
