x
Solved

# How to pickup the same digit in two column in Excel

Posted on 2016-09-29
Medium Priority
96 Views
How to pickup the same digit in two column in Excel?
For example:

A      B
3      2
4      4
6      7
9      9

Column A and B have two same digit, which are 4 and 9. How to pick up or label the same digit? Thank you
0
Question by:eemoon
• 5
• 4
• 2
• +1

LVL 53

Assisted Solution

Martin Liss earned 500 total points
ID: 41822014
This macro will find matches even if the numbers are not in the same row. It assumes that the data is in columns A and B.

``````Sub FindMatches()
Dim lngLastRow As Long
Dim lngRow As Long
Dim rngFound As Range

lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = 1 To lngLastRow
Set rngFound = Columns("B").Find(What:=Cells(lngRow, "A"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not rngFound Is Nothing Then
Cells(lngRow, "A").Interior.Color = vbYellow
rngFound.Interior.Color = vbYellow
End If
Next
End Sub
``````
0

Author Comment

ID: 41822040
Thank you for your fast reply. Do we have simple way to do that?
0

LVL 53

Assisted Solution

Martin Liss earned 500 total points
ID: 41822044
Are you asking how to use the macro? If so...

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window). If you don’t see an existing module then select Insert -> Module from the context menu. Otherwise just select the module.

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+A.

When you close the workbook you will need to save it as an xlsm or xls file if it’s not already one of those.
0

LVL 27

Assisted Solution

Glenn Ray earned 1000 total points
ID: 41822145
You can use Conditional Formatting to highlight values that occur in the other column.

In your example, you'd create a conditional formatting rule for each column that would check the cell's value against all cells in the other column.  If there was a match, the cell would highlight.

Here's just one way that could be implemented:

The formulas used are
for column A:
=MATCH(A2,\$B2:\$B1000,0)
for column B:
=MATCH(B2,\$A2:\$A1000,0)

See the attached example file.
EE-Q_28973318.xlsx
0

Author Comment

ID: 41822832
Thank you so much for your reply. I think your method is very good. but I could not be clear about the steps. Can you explain the steps in a little bit more detail? Thank you
0

LVL 36

Assisted Solution

Subodh Tiwari (Neeraj) earned 500 total points
ID: 41822871
If your intention is to find the common values from two lists i.e. values which are found on both the lists, you may refer to the attached and click the button called "Find Common Values" on Sheet1 to get the output in column D.
EE-Q_28973318.xlsm
0

LVL 27

Assisted Solution

Glenn Ray earned 1000 total points
ID: 41823893
eemoon,

If you've never used Conditional Formatting, you may want to get a basic understanding from Microsoft's online support:
https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

In my example, I created a conditional formatting rule using a formula ("Use a formula to determine which cells for format").  The formula should result in either a TRUE/FALSE condition or a non-zero/zero condition that will trigger the formatting.

For the cells in column A, I used the formula
=MATCH(A2,\$B\$2:\$B\$1000,0)
to find what position the value in A2 might occur in the array \$B\$2:\$B\$1000.  If there is a match, a positive number would result, triggering the conditional format.  If there is no match, the formula evaluates to zero and no formatting happens.

I then applied this format to all the existing cells in column A (=\$A\$2:\$A\$40).

Admittedly, I should have been more consistent with the MATCH formula by checking only rows 2 through 40.

Regards,
-Glenn
0

Author Comment

ID: 41825118
Hi Glenn,
Thank you so much for the explanation! It can work.
=MATCH(A2,\$B\$2:\$B\$1000,0)
it means that using A2 to compare with B2-B1000, right? what is 0 for?

the range I gave is "A2 to A40" and "B2 to B40" like what you did. But why it always is "A2 to B40" ? please see the picture.
Capture.PNG
0

LVL 27

Accepted Solution

Glenn Ray earned 1000 total points
ID: 41825342
In the MATCH function, the last argument indicates what kind of match one is looking for.  Zero means an exact match.

The Range I used (rows 2 through 40) was just for my example workbook.  You will want to change this range to match your own conditions.

-Glenn
0

Author Comment

ID: 41825385
What does the "1000" mean?

=MATCH(A2,\$B\$2:\$B\$1000,0)
0

LVL 27

Assisted Solution

Glenn Ray earned 1000 total points
ID: 41825464
It indicates the row number of the last cell in the range to check.  As I noted in my first post, my example data only went to row 40 and I probably should have written the MATCH functions to only check that far.

You should look up reference material on the MATCH function for more information.
0

Author Comment

ID: 41825537
Thank you Glenn!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.