Matching duplicate entries between two columns

[Pops Jackson]

I have one worksheet with several hundred entries in column "A" and over one
thousand items in column "C". I need a procedure which will determine if
each item in the shorter list is also found in the longer list and then paste
the duplicate item in the proper cell in column "B" so I can see which items
are duplicated and which are not.

The closest I can come is the following:

Windows("Deals.xls").Activate
Cells.Find(What:="Spot Buy Settled", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -2).Select ' Move to cell containing data


Set r1 = ActiveCell ' Copy data in two adjoining cells
Set r2 = ActiveCell.Offset(0, 1)
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select
Selection.Copy

Windows("New Deals.xls").Activate
Sheets("Statistics").Select
Cells.Find(What:="Spot Buy Settled", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -2).PasteSpecial ' Paste data in appropriate cells

This works great for short lists but for longer lists I need to go a step
further.

Thanks,
--
Pops Jackson

[Toppers]

You could use a formula in your shorter list:

Assumimg data starts in A2, then put this in B2 in shorter list and copy down

=IF(COUNTIF(Sheet2!$A$2:$A$1000,A2)>0, "Duplicated","")

Sheet2 contains the longer list

HTH

"Pops Jackson" wrote:

> I have one worksheet with several hundred entries in column "A" and over one
> thousand items in column "C". I need a procedure which will determine if
> each item in the shorter list is also found in the longer list and then paste
> the duplicate item in the proper cell in column "B" so I can see which items
> are duplicated and which are not.
>
> The closest I can come is the following:
>
> Windows("Deals.xls").Activate
> Cells.Find(What:="Spot Buy Settled", After:=ActiveCell,
> LookIn:=xlFormulas, LookAt _
> :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Activate
> ActiveCell.Offset(0, -2).Select ' Move to cell containing data
>
>
> Set r1 = ActiveCell ' Copy data in two adjoining cells
> Set r2 = ActiveCell.Offset(0, 1)
> Set myMultiAreaRange = Union(r1, r2)
> myMultiAreaRange.Select
> Selection.Copy
>
> Windows("New Deals.xls").Activate
> Sheets("Statistics").Select
> Cells.Find(What:="Spot Buy Settled", After:=ActiveCell,
> LookIn:=xlFormulas, LookAt _
> :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Activate
> ActiveCell.Offset(0, -2).PasteSpecial ' Paste data in appropriate cells
>
> This works great for short lists but for longer lists I need to go a step
> further.
>
> Thanks,
> --
> Pops Jackson

[Pops Jackson]

Thanks, Toppers. This does the job perfectly.
--
Pops Jackson


"Toppers" wrote:

> You could use a formula in your shorter list:
>
> Assumimg data starts in A2, then put this in B2 in shorter list and copy down
>
> =IF(COUNTIF(Sheet2!$A$2:$A$1000,A2)>0, "Duplicated","")
>
> Sheet2 contains the longer list
>
> HTH
>
> "Pops Jackson" wrote:
>
> > I have one worksheet with several hundred entries in column "A" and over one
> > thousand items in column "C". I need a procedure which will determine if
> > each item in the shorter list is also found in the longer list and then paste
> > the duplicate item in the proper cell in column "B" so I can see which items
> > are duplicated and which are not.
> >
> > The closest I can come is the following:
> >
> > Windows("Deals.xls").Activate
> > Cells.Find(What:="Spot Buy Settled", After:=ActiveCell,
> > LookIn:=xlFormulas, LookAt _
> > :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> > False, SearchFormat:=False).Activate
> > ActiveCell.Offset(0, -2).Select ' Move to cell containing data
> >
> >
> > Set r1 = ActiveCell ' Copy data in two adjoining cells
> > Set r2 = ActiveCell.Offset(0, 1)
> > Set myMultiAreaRange = Union(r1, r2)
> > myMultiAreaRange.Select
> > Selection.Copy
> >
> > Windows("New Deals.xls").Activate
> > Sheets("Statistics").Select
> > Cells.Find(What:="Spot Buy Settled", After:=ActiveCell,
> > LookIn:=xlFormulas, LookAt _
> > :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> > False, SearchFormat:=False).Activate
> > ActiveCell.Offset(0, -2).PasteSpecial ' Paste data in appropriate cells
> >
> > This works great for short lists but for longer lists I need to go a step
> > further.
> >
> > Thanks,
> > --
> > Pops Jackson