Forum Discussion
Remove duplicates within multiple columns without removing adjacent data
Yes, sorry, that is
=IFERROR( INDEX( A$2:INDEX(A:A,COUNTA(A:A)), AGGREGATE(15,6, 1/(COUNTIF(I$1:I1,A$2:INDEX(A:A,COUNTA(A:A)))=0)* (ROW(A$2:INDEX(A:A,COUNTA(A:A)))-ROW(A$1)),1 ) ),"")
Spoke too soon; the third column (column item C ) did not transfer all the data. That particular column did not have any duplicates though.
I removed $ for the first column of COUNTIF function and kept $ so that it follows the first row which is the item and so that the column/item may change if dragged and it looks to be working now.
Yes, sorry, that is
=IFERROR(
INDEX(
A$2:INDEX(A:A,COUNTA(A:A)),
AGGREGATE(15,6,
1/(COUNTIF(I$1:I1,A$2:INDEX(A:A,COUNTA(A:A)))=0)*
(ROW(A$2:INDEX(A:A,COUNTA(A:A)))-ROW(A$1)),1
)
),"")UNIQUE() is available for Office 365 subscribers only, on Mac as well. UNIQUE function - Office Support (microsoft.com)
SergeiBaklan
Sergei, is the UNIQUE() command part of Excel's functions, or are you referring to the function in shell scripts?
I'm running Excel on Mac, and I'm unsuccessful at removing duplicates from even the simplest test files (I don't have large data sets like some users.)- Understood, very cool, thank you!
You are welcome. If your Excel supports dynamic arrays, in previously attached file to the right of above formula generated range is the same using UNIQUE(), that's much easier.
- Thank you again!
