comparing items in two Columns...

scottbro

New Member
Joined
Feb 20, 2002
Messages
10
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
thanks...but how can I see which ones are matches? For example, is there someway to highlight those that match?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top