Forum Discussion
Compatibility error on older versions -Unique formula
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.
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,