VB Required??

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

www.OfficeArticles.com
www.MyExpertsOnline.com


"Ket" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

www.OfficeArticles.com
www.MyExpertsOnline.com


"Ket" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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/...soft_excel.htm
Then you can simply insert a reference number and copy down.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Ket" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>