Sorry, think there was an error in my earlier offering

In sheet: Summary,

In A2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))))

In A2 should be:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))+1))

.. It seems not to be putting all the unique data in place from the other

sheet,

Ok, going by the comment above, I've re-read your orig posting. I had

earlier assumed that you wanted only the combined shares lines to be

extracted over. I see now that you want all the uniques, ie inclusive of

those "single" lines in the source which doesn't require combining.

Here's the revised sample which should now accomplish it:

http://www.flypicture.com/download/NjE3Mg==
Combine lots sold on same day f same price f same stock_v2.xls

The revised construct

--------------------------

In source sheet: X,

In J2:

=IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G$2:G2=G2)*B$2:B2))

In K2:

=IF(A2="","",IF(OR(SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G$2:G2=G2))>1,SUMPRODUCT((A$2:A$20=A2)*(F$2:F$20=F2)*(G$2:G$20=G2))=1),ROWS($1:1),""))

Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of

source data. Col K serves to flag the all the "unique" lines to be copied

over to the summary sheet. Note that you need to change the ranges within

the 2nd sumproduct (A$2:A$20, F$2:F$20, etc) to suit the actual expected

extent of the source data. I used row20 as the extent arbitrarily.

In sheet: Summary,

A1:I1 carries the same col headers as in X

In A2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))+1))

In B2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!J:J,SMALL(X!$K:$K,ROWS($1:1))+1))

In C2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!C:C,SMALL(X!$K:$K,ROWS($1:1))+1))

In D2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!D

,SMALL(X!$K:$K,ROWS($1:1))+1))

In E2:

=IF(B2="","",B2*D2)

In F2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL(X!$K:$K,ROWS($1:1))+1))

In G2:

=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL(X!$K:$K,ROWS($1:1))+1))

In H2:

=IF(B2="","",G2*B2)

In I2:

=IF(B2="","",H2-E2)

Select A2:I2, fill down to cover the same extent as filled in X's cols J and

K.

As for your comment:

I'm going to want to go with some VBA code to do this task

and deposit the result as values in the new second sheet, though.

I'm not vba conversant enough to offer you this route. But perhaps if the

expected source data extent is something that is quite fixed, think you

could try recording a macro when you perform the construct steps outlined,

inclusive of a couple of additional steps for an entire sheet copy n paste

special as values to freeze values in Summary. Alternatively, I would

suggest you try a fresh posting in .programming for insights from responders

versed in vba. Since this thread is quite dated and deep, I'm not sure

whether there are other responders still tracking developments here <g>.

All the best ..

---