Is it possible in Excel?

[an]

Hello!
Sorry for my insistence.
With formulae

=IF(COUNTIF($B$1:B4;B4)>1;"Exist in cell " &ADDRESS(MATCH
(B4;$B$1:B4;0);COLUMN(B4);4);"")

I obtain always identification only of the 1st cell.
Ex:
Exist in cell A3
Exist in cell A3
Exist in cell A3, and so on

However, I would like, if is possible in Excel, to obtain
answer with indication about all cells where same data
exist (not only 1st cell).
Ex:
Exist in cell A3, A4, B2, C7, and so on.

Is it possible in Excel?
Thanks in advance.
an

 

[J.E. McGimpsey]

One way, using a User Defined Function:

Public Function ListDups(rng As Range, myCell As Range) As String
Const DELIM As String = ", "
Dim cell As Range
Dim sAddr As String

If Application.CountIf(rng, myCell) > 1 Then
For Each cell In rng.Resize(rng.Count - 1, 1)
If cell.Value = myCell.Value Then _
sAddr = sAddr & DELIM & cell.Address(False, False)
Next cell
ListDups = "Exist in cell " & Mid(sAddr, Len(DELIM) + 1)
Else
ListDups = ""
End If
End Function

Call as:

=ListDups($B$1:B4, B4)

Note: For large ranges, using a rng.Find(myCell.Value) and FindNext
loop may be much more efficient than looping through all the cells
in the range.

 

[an]

Ok!

Many thanks.
I supposed wich is impossible with function, Ok?
an