comparing items in two Columns...

[scottbro]

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

[Aladin Akyurek]

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

[scottbro]

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

[Dave Hawley]

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

[Jay Petrulis]

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

=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

[Aladin Akyurek]

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

[Don C]

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.

[Aladin Akyurek]

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

[Bikeit]

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