common numbers in 2 columns

[aubudgo]

I have two columns of numbers. One is a large list with say 3,000
random
serial numbers. The other is a smaller list of 800 serial numbers. I
want to
find if any serial numbers are listed in both of the columns. I want to
identify which number appear in both columns and move them to another
column, or at least highlight them. I tried to follow the advice given
to an earlier post but could not make it work for me: perhaps I need a
simpler or more detailed explanation.

[Max]

"aubudgo" wrote:
> I have two columns of numbers. One is a large list with say 3,000
> random serial numbers. The other is a smaller list of 800 serial numbers.
> I want to find if any serial numbers are listed in both of the columns. I want to
> identify which number appear in both columns and move them to another
> column, or at least highlight them ..

Try one way via non-array formulas, re this recent post:
http://tinyurl.com/rdhdf

Note that the formula in D2 is just a sweet copy across of the one placed in
C2. To compare it the other way around (compare col B against col A, and
slice the matched and unmatched items into cols C and D), just make a copy of
the original sheet (where col A is compared against col B), then swap the 2
source lists in cols A and B around.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[VBA Noob]

Hi

I've post an example of two ways to skin this cat

Also a useful link on Vlookup's
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm


http://cjoint.com/?hwkSkkmizO

VBA Noob

[excelent]

compare column B with A and color all cells ind A equal to cells in B

Sub dub()
Dim r As Single, s As Single
Range("a1:a3000").Interior.ColorIndex = xlNone
For r = 1 To 800
For s = 1 To 3000
If Cells(r, 2) = Cells(s, 1) Then If Cells(r, 2) <> "" Then Cells(s,
1).Interior.ColorIndex = 3
Next
Next

[Greg Wilson]

Highlight Duplicates with Conditional Formatting:-

Assuming that the first range is A1:A800 and the second range is in column B
starting at B1:
1. Select all the cells of the second range (column B)
2. Select Format > Conditional Formatting
3. Select "Formula Is" from the left dropdown
4. Paste this formula (use Ctrl + V to paste):
=COUNTIF($A$1:$A$800, B1) >0
5. Click the Format button and select the desired format option
6. Click OK to close the Format Cells dialog
7. Click OK to close the Conditional Formatting dialog

Note that if the either range will change in size then using a dynamic named
range is suggested. See http://www.contextures.com/xlNames01.html#Dynamic

Macro that Copies Duplicates to a Third Column:-

Sub CopyDups()
Dim r1 As Range, r2 As Range, r3 As Range
Dim c As Range

Set r1 = Range(Range("A1"), Range("A1").End(xlDown))
Set r2 = Range(Range("B1"), Range("B1").End(xlDown))
For Each c In r2.Cells
If Application.CountIf(r1, c) > 0 Then
If r3 Is Nothing Then
Set r3 = Range("C1")
r3 = c.Value
Else
If Application.CountIf(r3, c) = 0 Then
Set r3 = r3.Resize(r3.Count + 1, 1)
r3(r3.Count, 1) = c.Value
End If
End If
End If
Next
End Sub

Note that the above macro shouldn't list duplicates more than once if they
are repeated in the first (larger) range. Also note that it was written just
now (for you) in a hurry with minimal testing. It should be tested
rigorously. That's your job. Change range references to suit. Hope it does
the job.

Regards,
Greg


"aubudgo" wrote:

> I have two columns of numbers. One is a large list with say 3,000
> random
> serial numbers. The other is a smaller list of 800 serial numbers. I
> want to
> find if any serial numbers are listed in both of the columns. I want to
> identify which number appear in both columns and move them to another
> column, or at least highlight them. I tried to follow the advice given
> to an earlier post but could not make it work for me: perhaps I need a
> simpler or more detailed explanation.
>
>

[Max]

> .. just make a copy of the original sheet (where col A is compared
> against col B), then swap the 2 source lists in cols A and B around.

To "swap", in the copied sheet, clear cols A and B (select the cols and
press Delete key), then go back to the original sheet and copy col A, paste
it into col B in the copied sheet, then repeat to copy & paste col B from the
original into col A in the copied sheet. Then just ensure that the formula
fills are sufficient to cover the new data extent in col A. Do not delete
the cols as this will foul up the formulas.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---