#Solved How to get distinct values in Excel (unique + 1st duplicate occurrences) regular expr - or array?
#Solved How to get distinct values in Excel (unique + 1st duplicate occurrences) regular expr - or array?
Office2016 formulas not working? O365 result is ok.
Although I’m on O365 right now, I need it to work on O2016.
Are there any restrictions to the length of a string, or am I doing something wrong? Because this is the only case where formula is not working – other numbers etc. are shorter than 20.
Replies
Yes, strings that contain only numbers and exceed 15 significant digits will cause a problem due to the fact that the sumif(s) and countif(s) functions will apply Excel's 15 digit character limit, where the numbers after the 15th digit are converted to 0. And, these functions convert the strings to numbers internally, so it does not matter that you have them formatted as text (if the text could not be converted to a number, then these functions work fine). See screenshot for example - the last 3 digits are different, but countif is treating them as the same.
I think this is one alternative to doing what you're asking: =IFERROR(INDEX(Table1[ASNnro], SMALL(IF(MATCH(Table1[ASNnro], Table1[ASNnro], 0)=ROW(INDIRECT("1:"&ROWS(Table1[ASNnro]))), MATCH(Table1[ASNnro], Table1[ASNnro], 0)), ROW(INDIRECT("1:"&ROWS(Table1[ASNnro]))))), "")
Thank you, will try this asap.
worked, thanks again!
