Solved How to find the same numbers in Excel 2010

Dell - inspiron 17.3" laptop - 4gb memor...
August 8, 2014 at 17:19:02
Specs: Windows 8.1, AMD A8/4.0Gb
I have two columns of ID numbers, A1:750 and B1:750. In each columns there are some IDs that are the same but also some IDs that are different. When all of the IDs in both columns are the same, I sorted them in ascending order and then used =EQUAL to see if they were the same. Since both columns do not contain the same IDs, this approach won't work. Is there an approach that I could use that does not require the use of a macro as I do not know how to write/use a macro?

Thank you.
Brian W


See More: How to find the same numbers in Excel 2010

Report •

#1
August 8, 2014 at 18:49:19
✔ Best Answer
There are several ways to check for duplicates, here's two:

In cell C1 enter the formula:

=IF(ISERROR(MATCH(A1,$B$1:$B$750,0)),"no match in Col B","")

Drag down 750 rows

This will tell you if a Number in Column A
does NOT have a matching number in Column B.

A second way is to use Conditional Formatting

Conditional Formatting 2007

1) Select your cell or range of cells: A1:B750
2) On the ribbon click Conditional Formatting
3) Click on Highlight Cell Rules,.
4) Click Duplicate Values.
5) In the input box, Select Unique Values
9) Click OK

Those values that are Unique, with no matches should be highlighted.


See how those work for you.

MIKE

http://www.skeptic.com/


Report •

#2
August 9, 2014 at 07:23:19
Mike,

Thank you for your response. I was unable to get the first method to work but the second with conditional formatting worked as needed.

Thanks again,
Brian W


Report •

#3
August 11, 2014 at 04:28:42
Mike,

Once duplicates are identified in Col A and Col B using conditional formatting, is there a way to copy the duplicates into another column?

In the end, Column A would have its set of original ID numbers, Column B would have its set of original ID numbers and Column C would have only those numbers found in both Column A and Column B, e.g., the duplicates.

Thank you.
Brian W


Report •

Related Solutions

#4
August 11, 2014 at 08:04:12
So you want all the Duplicates, but NOT the Unique numbers?

MIKE

http://www.skeptic.com/


Report •

#5
August 11, 2014 at 09:19:00
Mike,

Yes, I need to know what numbers were duplicates; not those that were unique.

Thank you.
Brian W


Report •

#6
August 11, 2014 at 09:32:27

Report •

#7
August 11, 2014 at 09:36:00
If you can not insert a column then this modifed version should work.

Keep A & B the same, put the formula in column C

=IF(ISERROR(MATCH(A1,$B$1:$B$750,0)),"",A1)

MIKE

http://www.skeptic.com/


Report •

#8
August 11, 2014 at 11:14:52
I know you said that you don't know how to write or use a macro, but it's a skill worth investigating. This code will place all of the duplicate values in Column C, without the blank rows that you will get with a formula based method.

To help you understand how the code works, you can review this tutorial:

http://www.computing.net/howtos/sho...

Option Explicit
Sub ExtractDups()
Dim sh As Worksheet
Dim lastA_row, lastB_row, dstRow As Long
Dim fVal, d As Range
'Determine last row with data in Columns A & B
   lastA_row = Range("A" & Rows.Count).End(xlUp).Row
   lastB_row = Range("B" & Rows.Count).End(xlUp).Row
'Initialize variable for Destination Row
   dstRow = 1
'Search for duplicate data between Columns A & B
    Set sh = Sheets(1) '***Assumes first sheet, Edit as required***
        For Each d In sh.Range("A2:A" & lastA_row) 'Assumes header row
            Set fVal = sh.Range("B2:B" & lastB_row).Find(d.Value, _
                       LookIn:=xlValues, LookAt:=xlWhole)
'If duplicate is found, increment Destination Row, put duplicate value in Column C
                If Not fVal Is Nothing Then
                    dstRow = dstRow + 1
                    sh.Range("C" & dstRow) = d
                End If
        Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
August 13, 2014 at 11:18:06
Mike and DerbyDad03,

Thank you for your suggestions. I can now find the duplicates.

In regard to learning how to write/use macros, I read the tutorial that DerbyDad03 wrote but it was too advanced for me. Can you recommend something that is written for someone that nothing about macros?

Thank you once again.
Brian W


Report •

#10
August 13, 2014 at 14:19:55
Brian, here is an online one:

http://www.wiseowl.co.uk/blog/s161/...

I'm sure DerbyDad can recommend more.

MIKE

http://www.skeptic.com/


Report •

#11
August 15, 2014 at 10:35:11
Brian,

I suggest that you Google around for a VBA tutorial that you find to your liking. I can't recommend any particular site because I was basically self-taught via trial and error, asking questions in forums, downloading code and running it to see what it did, etc.

The tutorial that I wrote was simply a list of the debugging techniques that I learned when I first starting playing around with VBA. The Single Step technique (F8) is by far the most useful of the debugging tools, not only as a means to check/test code that you write on your own, but also as a means to figure out what is going on with code you find on the web or that someone offers as a solution to an issue in a forum.

I'd be more than willing to help you get started, so feel free to ask questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question