Forum Discussion
Compatibility error on older versions -Unique formula
- 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.
Zed69 Or perhaps a few pivot tables could help generate the unique lists for you. See attached.
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,
