PDA

View Full Version : Worksheet Formula Finding Duplicate Names?



Simon Lloyd
01-22-2007, 04:25 PM
Hi all i have the formula below to check for duplicate names in a range which works fine
=IF(COUNTIF(Crewing!B$1:Q33,A3)>1,"Duplicated","Ok")what i want to be able to do is have a formula on another worksheet that will display the offset name if "Duplicated" is found, below is something i tried but of course doesnt work!
=if('Names & Areas'!B3:B89="Duplicated",(offset(,0,-1,,)),"")also if the cell containing this formula gives the duplicated name is it possible to also display the duplication cell address?

It is possible that 5 or 6 names may be duplicated so i would need to copy the formula down 5 to 6 cells enabling each duplication to be shown any ideas?

Regards,
Simon

P.S i realise this is an easy fix in VBA but would like a worksheet formula solution.

Simon Lloyd
01-22-2007, 04:40 PM
Showing the cell address is now not important!, i have been fiddling with conditional formatting and come up with this =IF(COUNTIF(Range1, B1)>1,TRUE,FALSE)which i use as the formula in CF, give my CF a Colour and i now get a visual of duplicated names!, so its possible that my question is superfluous but i would like to know how its done!

Regards,
Simon

mdmackillop
01-22-2007, 04:59 PM
While not a solution to your question, have you tried the Duplicate Manager add-in?
http://members.iinet.net.au/~brettdj/

Simon Lloyd
01-22-2007, 05:03 PM
Thanks MD, but its not a viable solution as im on a network and all machines that access the workbook would need to have the add in installed on them - what a nightmare that would be!

Regards,
Simon

Bob Phillips
01-22-2007, 05:08 PM
Here is one way.

Create a grid the same size as your original grid, and enter


=IF(ISERROR(SMALL(IF(COUNTIF($B$1:$Q$33,B$1:B$33)>1,ROW($A$1:$A$33),""),ROW($A1))),"",
INDEX(B$1:B$33,SMALL(IF(COUNTIF($B$1:$Q$33,B$1:B$33)>1,ROW($A$1:$A$33),""),ROW($A1))))

as an array formula in the first cell and copy down and across

Simon Lloyd
01-22-2007, 06:42 PM
Thanks Bob, the solution you posted it seems would produce a duplicate name in the new grid, quite neat but that would require the user to switch sheets to view duplicates and their location, what i would like to do is just have 5 to 6 cells in a column on the original sheet (Crewing) where a duplicated name would appear if found in B1:Q33.

Please don't bust a gut over this one as the conditional format i have already highlights the duplicated cells, it's just one of those "....wouldn't it be nice if...."

Kind regards,
Simon