Worksheet Formula Finding Duplicate Names?

[Simon Lloyd]

Hi all i have the formula below to check for duplicate names in a range which works fine
[VBA]=IF(COUNTIF(Crewing!B$1:Q33,A3)>1,"Duplicated","Ok")[/VBA]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!
[VBA]=if('Names & Areas'!B3:B89="Duplicated",(offset(,0,-1,,)),"")[/VBA]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]

Showing the cell address is now not important!, i have been fiddling with conditional formatting and come up with this [VBA]=IF(COUNTIF(Range1, B1)>1,TRUE,FALSE)[/VBA]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]

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

[Simon Lloyd]

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]

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]

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