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

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

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