Frenchie
i can't write that formula in excel. using a macro (program) its fairly easy. I assume you are not used to using macros.
open a new worksheet in excel. if you don't like this just don't save the file and its gone.
with worksheet open press alt f8 name the file duplicates and click on create then paste the following file on the line after sub duplicates() and before end sub
should look like this
Sub duplicates()
Dim dn(10)
For x = 1 To 10
a = Cells(2, x)
dn(a) = 1 ' only matching #'s are marked and automatically sorted
Next x
t = 1
For x = 1 To 10
If dn(x) = 1 Then Cells(5, t) = x: t = t + 1
Next x
For x = 1 To t - 1 ' t lets me know how many to check
Ln = 7
a = Cells(5, x) ' a=# i'm looking for
For y = 1 To 10 ' where i'm going to look
b = Cells(2, y)
If a = b Then Cells(Ln, x) = y: Ln = Ln + 1
Next y
Next x
End Sub
if your name and end sub appear twice then delete one set. click the x to close the window.enter your info on line 1 and 2
press alt f8, highlite the file name if it's not alredy marked then click run. change your data and repeat this line. there is a much easier way to start this file if you want to use it.