Find duplicates
 

I'm not sure if this involve program or just a function, but here's wha
my situation is.

I have three columns: A,B,C. Column A have 10 records, B have 100, an
C 100. I want to find if the ID in column A will match the ID in colum
C, if it is match, copy the contents in column B to column D. Is ther
any formula that will do this sort of things?

Any suggestion is greatly appreciated!

ljCharli

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

PLACE THIS FORMULA INTO COLUNM D

=IF(A1=C1,B1,"")

Once you have placed it into the first Dcell just copy it down for al
values

the cell ref should just change automactly.

What it is say is if the contents of cell a1 are equal to c1 the cop
b1 into d1. If this is not true then leave contents of cell d1 blank.

you should be able to cange this for differ things

ie < is less then
is more then

<= less then or equal
= greater or equal
= eqa

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

Many thanks for your help. I see your point; however, the problem i
that cell A only have 10 records and cell B and C have 100 records. I
I do this =IF(A1=C1,B1,"") what happens when column A reaches the cel
11 where 11 to 100 is blank? Here's what I want to do. Find the matc
from column A1 in anywhere on column C1 to C100. With the formula yo
provide, it only finds matching cels up to cell 10.

Any other suggestion is much appreciated.

Charli

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

you could have a loop macro run either manually or in worksheet_change

am not really very good with loops so you may need to ask some one bu
this is what the crude code would look like:

You can do long if formula but only up to a certain point
i.e. =IF(A1=C1,B1,IF(A2=C1,B1,IF(A3=C1,B1,"")))
only worked for eight

If Range("A1") = Range("C1") Then GoTo CC Else
If Range("A2") = Range("C1") Then GoTo CC Else
If Range("A3") = Range("C1") Then GoTo CC Else
If Range("A4") = Range("C1") Then GoTo CC Else
If Range("A5") = Range("C1") Then GoTo CC Else
If Range("A6") = Range("C1") Then GoTo CC Else
If Range("A7") = Range("C1") Then GoTo CC Else
If Range("A8") = Range("C1") Then GoTo CC Else
If Range("A9") = Range("C1") Then GoTo CC Else
If Range("A10") = Range("C1") Then GoTo CC Else Exit Sub
CC:
Range("B1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("A1").Selec

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

Many thanks for the help. By the way, is there way to determine th
number of rows so I can use a For loop instead of writing all that? Th
reason is I'm not only doing for 10 or 100...actually my projec
involves 48 thousand records that I need to check for duplicate an
copy the unique ID to column D.

ljCharli

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

Okay, here's something I have.....still not working...but perhap
someone else in here might be able to point where it's not working.


Sub Matching()
Dim A As Range, B As Range, C As Range, D As Range
Set C = Range("C2:C10")
Set B = Range("B2:B20")

For i = 2 To C.Rows.Count
For j = 2 To B.Rows.Count
If C.Cells(i, j) = B.Cells(i, j) Then
A.Cells(i, j).Select
Selection.Copy
D.Cells(i, j).Select
ActiveSheet.Paste
End If
Next j
Next i
End Sub


The error is Object variable or With block variable not set.

ljCharli

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

Here's the data I'm testing or working on.

A B C D
ID regID ConstID Unique ID
1 123 124
2 124 155
3 125 125
4 126 126
5 127 130
6 128 166
7 129 136
8 130 140
9 131 128
10 132
11 133
12 134
13 135
14 136
15 137
16 138
17 139
18 140
19 141

I'm trying to search column B if there is any number that will matc
column C, if it is, copy the corresponding value from column A t
column D.

ljCharli

--
Message posted from http://www.ExcelForum.com

Find duplicates
 

The reason this appears is because you have not set A or

--
Message posted from http://www.ExcelForum.com