Help with Sorting\Duplicates

New Contributor

Hi there, I'm trying to group duplicates but excel does not see A020601E and A02-06-01E as a duplicate due to the dashes. Is there a way to trick it into thinking this ?

 

2 Replies

@MOMO1790 

 

A few options might work for you depending on version and data set.

 

Formula (see attached xlsx):

=LET(arr,BYROW(D2:D11,LAMBDA(row,TEXTJOIN(,,TEXTSPLIT(row,"-")))),SORT(arr))

 

You might also consider creating a custom list and then sorting on the custom list if the number of unique items you have is reasonable.

Sort data using a custom list (microsoft.com)

thank you, this formula will at least allow me to now highlight duplicate values and count available space faster.