Below is a possible solution to the problem Challenge 67 – Find Duplicates and Show the Count
Use a helper column in which put following formula –
=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),
" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),
SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)
In the last column, put following formula –
=IF(COUNTIF($B$1:B2,B2)=1,COUNTIF(B:B,B2),"")
The solution work can be downloaded from Solution – Find Duplicates and Show the Count