Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulating Lists (Excel 97)

    Column A contains a list of all my customers, and Column B those customers who have purchased recently. I would like Column C to contain those customers who have not purchased recently, in other words, Column C = Column A - Column B. Both lists are sorted, if that helps. I have written a macro to do this, but it seems to me there ought to be a simpler way to do it.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Lists (Excel 97)

    you can use the following simple macro to get old customers into col C
    First Name items in Col A as ListA, like for Col B.
    You probaly will then want to sort col C to get rid of the blanks.


    Sub OldCustomers()

    Dim oCell As Range
    For Each oCell In Range("ListA")
    If Application.IsNA(Application.Match(oCell, Range("ListB"), False)) Then
    oCell.Offset(0, 2) = oCell.Value
    End If

    Next

    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Lists (Excel 97)

    better yet use this line:

    oCell.Offset(0, 2).End(xlUp).Offset(1, 0) = oCell.Value

    instead of
    oCell.Offset(0, 2)= oCell.Value

    This way no blanks will be left

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Lists (Excel 97)

    Boy, you people really know your Excel. I wrote the following, rather pedestrian macro:

    ' Define row counters
    Dim iA As Integer, iB As Integer, iC As Integer
    ' First row contains heading, so start with second row
    iA = 2
    iB = 2
    iC = 2

    ' Last Cells in Col A and Col B contain ZZZ
    Do Until Cells(iA, 1) = "ZZZ" And Cells(iB, 2) = "ZZZ"
    If Cells(iA, 1) = Cells(iB, 2) Then
    'Names match, just go to next row
    iA = iA + 1
    iB = iB + 1
    Else
    'Col A contains a unique name, move
    Cells(iC, 3) = Cells(iA, 1)
    iA = iA + 1
    iC = iC + 1
    End If
    Loop
    '
    End Sub

    but I like your much simpler one.

Posting Permissions

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