One fairly easy way is to use datafilteradvanced filterunique values only
and copy that list to the other list and copy the formula down. A macro can
automate this process.

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
Thanks Don that worked great now I have my one line for each Security ID.
I
have one last question though is there a way that I can make it so that
every
time there is a change in the Security Id # that it copies down that
security
Id and the corresponding total figure to my second sheet. For instance I
want it to search through the first sheet see 2824100 and copy it to my
second sheet and put the total amount in column (D). Then find 02209S103
and
copy that on the next line down and put the total in column (D) and so on
until it finishes copying all of the security Id's onto the new sheet.


"Don Guillett" wrote:

Why not just a sumproduct formula to get your sum?
=sumproduct((sheet1!a2:a22="joe")*(sheet1!b2:b22=1 )*sheet1!d2:d22)
or
=sumproduct((sheet1!a2:a22=a1)*(sheet1!b2:b22=1)*s heet1!d2:d22)

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
I am trying to create a macro that when run will take information from
one
sheet and copy it into another. On the first sheet called Multi basis
report
there are 4 columns. (B) Security Id, (C) Security, (D) Realized G/L,
and
(E)
Total Gain/Loss. I need to take the security Id #, the security name,
and
it's realized gain/loss and paste it onto a sheet named 2006 Sched D
changes
for transfers. The issue I am having is that on my first sheet there
are
multiple lines for each security Id instead of just one consolidated
line.
I
have created subtotals for each security so there is a single line with
the
security Id and the total column summed up. Any help that can be
offered
is
greatly appreciated.

Thanks in advance,
Brian Monaghan