?
Solved

How to pickup the same digit in two column in Excel

Posted on 2016-09-29
12
Medium Priority
?
79 Views
Last Modified: 2016-10-02
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
Comment
Question by:eemoon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 49

Assisted Solution

by:Martin Liss
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

Open in new window

0
 

Author Comment

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

Assisted Solution

by:Martin Liss
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Assisted Solution

by:Glenn Ray
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:
EE-ConditionalFormatting-Matches.png
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

by:eemoon
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 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
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

by:Glenn Ray
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

by:eemoon
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

by:
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

by:eemoon
ID: 41825385
What does the "1000" mean?

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

Assisted Solution

by:Glenn Ray
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

by:eemoon
ID: 41825537
Thank you Glenn!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question