+ Reply to Thread
Results 1 to 10 of 10

VB Required??

  1. #1
    Anne Troy
    Guest

    Re: 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" <ketkana@tiscali.co.uk> wrote in message
    news:18qic1145jpm4egn5nqrpb7bqvqt9s2n30@4ax.com...
    > 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




  2. #2
    Bob Phillips
    Guest

    Re: VB Required??

    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" <ketkana@tiscali.co.uk> wrote in message
    news:18qic1145jpm4egn5nqrpb7bqvqt9s2n30@4ax.com...
    > 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




  3. #3
    Matt Lunn
    Guest

    RE: VB Required??

    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
    >


  4. #4
    Anne Troy
    Guest

    Re: 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" <ketkana@tiscali.co.uk> wrote in message
    news:18qic1145jpm4egn5nqrpb7bqvqt9s2n30@4ax.com...
    > 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




  5. #5
    Bob Phillips
    Guest

    Re: VB Required??

    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" <ketkana@tiscali.co.uk> wrote in message
    news:18qic1145jpm4egn5nqrpb7bqvqt9s2n30@4ax.com...
    > 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




  6. #6
    Matt Lunn
    Guest

    RE: VB Required??

    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
    >


  7. #7
    Ket
    Guest

    VB Required??

    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

  8. #8
    Anne Troy
    Guest

    Re: 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" <ketkana@tiscali.co.uk> wrote in message
    news:18qic1145jpm4egn5nqrpb7bqvqt9s2n30@4ax.com...
    > 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




  9. #9
    Bob Phillips
    Guest

    Re: VB Required??

    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" <ketkana@tiscali.co.uk> wrote in message
    news:18qic1145jpm4egn5nqrpb7bqvqt9s2n30@4ax.com...
    > 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




  10. #10
    Matt Lunn
    Guest

    RE: VB Required??

    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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1