Solved

How to pickup the same digit in two column in Excel

Posted on 2016-09-29
12
53 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 45

Assisted Solution

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

Assisted Solution

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

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

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 27

Assisted Solution

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now