Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: comparing items in two Columns...

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hello,

    I have two columns of data, which contain part numbers. I would like to compare the two colums to see if there are any of the same entries in each (ie- to see what, if any, part numbers exist in Column B that also exist in A) Anybody know a way to help identify this? Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-22 08:26, scottbro wrote:
    hello,

    I have two columns of data, which contain part numbers. I would like to compare the two colums to see if there are any of the same entries in each (ie- to see what, if any, part numbers exist in Column B that also exist in A) Anybody know a way to help identify this? Thanks
    =SUMPRODUCT(ISNUMBER(MATCH(A10:A13,B10:B13,0))+0)

    the result of which is either 0 (meaning no common items) or >0 (meaning N types of items are common to both lists).

    Aladin

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks...but how can I see which ones are matches? For example, is there someway to highlight those that match?

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Scott

    Conditional Formatting would do it quite easy.

    http://www.ozgrid.com/Excel/Formulas.htm

    Some examples here that could help.


    You may also want to look at Data>Filter>Advanced Filter to get only uniques


  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 08:34, Aladin Akyurek wrote:
    On 2002-03-22 08:26, scottbro wrote:
    hello,

    I have two columns of data, which contain part numbers. I would like to compare the two colums to see if there are any of the same entries in each (ie- to see what, if any, part numbers exist in Column B that also exist in A) Anybody know a way to help identify this? Thanks
    =SUMPRODUCT(ISNUMBER(MATCH(A10:A13,B10:B13,0))+0)

    the result of which is either 0 (meaning no common items) or >0 (meaning N types of items are common to both lists).

    Aladin
    Hi All,

    Slightly shorter:

    {=SUM(COUNTIF(A10:A13,B10:B13))}
    array entered

    or

    =SUMPRODUCT(COUNTIF(A10:A13,B10:B13))
    entered normally

    ...would return the number of duplicate items found.

    The suggestion to use Conditional Formatting to highlight the actual duplicates, paired with a count formula would work rather well, I would think.

    Bye,
    Jay

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-22 08:41, scottbro wrote:
    thanks...but how can I see which ones are matches? For example, is there someway to highlight those that match?
    The formula I proposed gives you a diagnostic result in accordance with my interpretation of your original question.

    You could use conditional formatting for the current question.

    Using A10:A13 and B10:B13 as your two column ranges:

    select A10:A13,
    activate Format|Conditional Formatting,
    choose 'Formula Is' for 'Condition 1',
    enter in the formula box:

    =COUNTIF($B$10:$B$13,A1)>0

    activate Format,
    select a color on the Patterns tab,
    click OK, OK.

    Highlighted cells in A-range indicates values that also occur at least one in B-range.

    Aladin

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or in a third column, you could use COUNTIF.

    If Part List 1 is, let's say A1:A30 and Part List 2 is B1:B30, then in C2
    =Countif($A$2:$A$30,B2)
    and copy that down to C30.

    A "1" in column C is a unique Part 2 number and any number >1 in column C is a Part 2 number found in Columnn A.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Jay, that's right. When writing up in high speed, the prob of match getting out is, with me, slightly higher than that of countif.

    Aladin

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is what I do that may be some use to you.
    It is a Macro that allows you to mark things at the same time as identifying matches.
    In this case there is a source sheet and two compare sheets.

    Sub Find_Matching_Invoice_Numbers()

    Dim Search_Invoice_Number As String
    Dim Compare_Invoice_Number As String

    Dim InputRow(3), InputCol(3), MarkerCol(3)
    Dim Search_Sheet2RowReset
    Dim Search_Sheet3RowReset
    Dim DataSheets(3) As String
    Dim Sheet_loop_counter
    Dim DataFinish As String
    Dim Check As String
    Dim MatchWith As String

    'Makesure that each Input Col has 'End' placed at the bottom VERY IMPORTANT
    'else the macro will not stop

    'Initialise variables
    DataSheets(1) = "Latest Source Sheet" 'source sheet
    DataSheets(2) = "Compare sheet one" 'search sheet
    DataSheets(3) = "Compare sheet two" 'search sheet

    'Input rows and cols to be determined by user.
    InputRow(1) = 6
    InputCol(1) = 1
    MarkerCol(1) = 12

    InputRow(2) = 4
    Search_Sheet2RowReset = InputRow(2)
    InputCol(2) = 2
    MarkerCol(2) = 10

    InputRow(3) = 8
    Search_Sheet3RowReset = InputRow(3)
    InputCol(3) = 2
    MarkerCol(3) = 8

    DataFinish = False

    Do 'outer loop Until DataFinish = True

    Sheets(DataSheets(1)).Select
    Cells(InputRow(1), InputCol(1)).Select

    Search_Invoice_Number = ActiveCell.Value

    If Search_Invoice_Number = "End" Then
    DataFinish = True
    Exit Do
    End If


    For Sheet_loop_counter = 2 To 3 'Note if you want to just compare one sheet against the other the reduce the loop e.g. 2 to 2.

    Check = True

    Sheets(DataSheets(Sheet_loop_counter)).Select

    Do ' inner loop Until Check = False

    Cells(InputRow(Sheet_loop_counter), InputCol(Sheet_loop_counter)).Select
    Compare_Invoice_Number = ActiveCell.Value

    If Compare_Invoice_Number = "End" Then
    Check = False ' Set value of flag to False.
    Exit Do
    ElseIf Search_Invoice_Number = Compare_Invoice_Number Then
    Selection.Font.ColorIndex = 3
    Selection.Font.Bold = True 'set found cell text to bold red
    Cells(InputRow(Sheet_loop_counter), MarkerCol(Sheet_loop_counter)).Select
    ActiveCell = "Match with source file"

    Sheets(DataSheets(1)).Select
    Cells(InputRow(1), InputCol(1)).Select
    Selection.Font.ColorIndex = 3
    Selection.Font.Bold = True 'set cell in source text to bold red
    Cells(InputRow(1), MarkerCol(1)).Select
    MatchWith = "Match with "
    MatchWith = MatchWith + DataSheets(Sheet_loop_counter)
    ActiveCell = MatchWith

    Check = False ' Set value of flag to False.
    Exit Do
    End If

    InputRow(Sheet_loop_counter) = InputRow(Sheet_loop_counter) + 1

    Loop Until Check = False ' Exit inner loop immediately.


    Next Sheet_loop_counter

    InputRow(1) = InputRow(1) + 1
    InputRow(2) = Search_Sheet2RowReset
    InputRow(3) = Search_Sheet3RowReset

    Loop Until DataFinish = True

    End Sub





Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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