Hello,

I am using excel 2003 on windows xp.

I have a column of information that contains the values 'YES', 'NO, or
'DUPLICATE'.

How can I create a formula that looks at all instances of YES and
provides a unique reference number (eg000001,000002 etc) for that
instance in the adjacent cell. The same applies for every instance of
'NO'.
Where a duplicate record exists, that entire row needs to be deleted.

Any assistance offered, gratefully recieved.

Many thanks.

Ket
London, UK

Well, you'll likely want to delete the duplicates first:
http://www.officearticles.com/excel/...ft_excel. htm
Then you can simply insert a reference number and copy down.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Ket" wrote in message
...
Hello,

I am using excel 2003 on windows xp.

I have a column of information that contains the values 'YES', 'NO, or
'DUPLICATE'.

How can I create a formula that looks at all instances of YES and
provides a unique reference number (eg000001,000002 etc) for that
instance in the adjacent cell. The same applies for every instance of
'NO'.
Where a duplicate record exists, that entire row needs to be deleted.

Any assistance offered, gratefully recieved.

Many thanks.

Ket
London, UK

Ket,

This tests for the value in column A, and puts the id in B. It is case
agnostic.

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim cYes As Long
Dim cNo As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If LCase(Cells(i, "A").Value) = "duplicate" Then
Rows(i).Delete
End If
Next i

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If LCase(Cells(i, "A").Value) = "yes" Then
cYes = cYes + 1
With Cells(i, "B")
.Value = cYes
.NumberFormat = "000000"
End With
ElseIf LCase(Cells(i, "A").Value) = "no" Then
cNo = cNo + 1
With Cells(i, "B")
.Value = cNo
.NumberFormat = "000000"
End With
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ket" wrote in message
...
Hello,

I am using excel 2003 on windows xp.

I have a column of information that contains the values 'YES', 'NO, or
'DUPLICATE'.

How can I create a formula that looks at all instances of YES and
provides a unique reference number (eg000001,000002 etc) for that
instance in the adjacent cell. The same applies for every instance of
'NO'.
Where a duplicate record exists, that entire row needs to be deleted.

Any assistance offered, gratefully recieved.

Many thanks.

Ket
London, UK

Ket,

Will you be adding more data to the column? That is, do you want to assign a
new reference every time a new value of YES, NO or DUPLICATE is added?


Matt

"Ket" wrote:

Hello,

I am using excel 2003 on windows xp.

I have a column of information that contains the values 'YES', 'NO, or
'DUPLICATE'.

How can I create a formula that looks at all instances of YES and
provides a unique reference number (eg000001,000002 etc) for that
instance in the adjacent cell. The same applies for every instance of
'NO'.
Where a duplicate record exists, that entire row needs to be deleted.

Any assistance offered, gratefully recieved.

Many thanks.

Ket
London, UK