Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing cell data (2003)

    Hi,

    Im trying to compare two columns of data, A & B. If there is some data in the cells in column B that isnt in the cells in A then I need to list this in column C ! Im quite new to the macro world and therefore any suggestions would be appreciated.

    Cheers

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing cell data (2003)

    You don't need a macro for this, you can use a formula. Say that the data in column A are in A1:A100. The formula
    =MATCH(B1,$A$1:$A$100,0)
    in C1 will return a number if the value in B1 can be found in A1:A100, and the error value #N/A (not available) otherwise. You can modify the way the result is displayed, for example:
    =IF(ISNA(MATCH(B1,$A$1:$A$100,0)),"X","")
    will display an X is the value is not found, and leave the cell blank otherwise. You can fill down the formula as far as needed. See attached example.

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing cell data (2003)

    Hi,

    Thanks for the help, but ideally, with your example, "Dewitt" should be listed in column C as this name is in column B but not A. Is it possible for this to be listed in C?

    Hope this doesnt sound too confusing.

    Regards
    Badger1

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing cell data (2003)

    Do you want to list the names in C next to the same name in B, or should they all be at the top, starting in C1?

  5. #5
    New Lounger
    Join Date
    May 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing cell data (2003)

    The data in column C can start at the top, the list Im comparing is very long so it could well be the case that column C will only contain a few entries.

    Cheers.
    Badger1

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing cell data (2003)

    Here is a macro solution. The code loops through the populated cells in column B and each time it finds an unmatched name, adds it to column C.

    Warning: column C is cleared at the beginning of the macro.

    Sub FindNonMatching()
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCurRow As Long
    Range("C:C").Clear
    lngMaxRow = Range("B65536").End(xlUp).Row
    For lngRow = 1 To lngMaxRow
    If VarType(Application.Match(Range("B" & lngRow), _
    Range("A:A"), 0)) = vbError Then
    lngCurRow = lngCurRow + 1
    Range("C" & lngCurRow) = Range("B" & lngRow)
    End If
    Next lngRow
    End Sub

  7. #7
    New Lounger
    Join Date
    May 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing cell data (2003)

    That works great.

    Thanks very much HansV for your time, its much appreciated.

    Best Regards
    Badger1

Posting Permissions

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