In . com, Ken
Johnson spake thusly:

Hi Dallman,

After re-examining your post and noticing that you columns E and I are
either hidden or contain irrelevant data, it might make it easier for
you if I supply the new equations based on the inclusion of columns E
and I...

Yup. Thanks for noticing.

I didn't yet reply to your other posts because I was conducting some
experiments on your efforts and because I was busy with some other
tasks. I really appreciate your yeomen work helping me solve my
complex problem.

I had adjusted your equations for columns -- by copying my sheet
to a dummy sheet and deleting the columns I had not published here,
creating your suggested columns, then inserting the columns I'd
deleted in order to shift the ranges in your equations. I also
did figure out I needed to extend the rows. thanks. I do very much
appreciate the new offerings, in any case. Much better to all be
working from the same sheet of "music," as they say.


Column L....

Heading is "DATE (sale per date per price)"
Formula in row 2 is
=IF(SUMPRODUCT(($B2:$B$9=B2)*($H2:$H$9=H2)*($J2:$J $9=J2))<1,"",H2)

Now for a bit of bad news: this isn't working entirely. I have
only a vague theory as to why. But I think the algorithm will need
revision. :-(

Let's just look at this snippet:

r B C D ... F G H ... J K
o
w Symbl # Date Bot Bot @ Cost Date Sld Sld @ Proceeds
-- ----- --- --------- ----- -------- -------- ----- --------
24 CSCO 200 3-Feb-06 18.03 3,606.00 8-Feb-06 19.48 3,895.88
25 CSCO 250 8-Jun-06 19.52 4,880.00 9-Aug-06 19.40 4,849.84
26 CSCO 240 11-Jul-06 18.76 4,502.40 9-Aug-06 19.40 4,655.85
27 CSCO 20 11-Jul-06 18.76 375.20 9-Aug-06 19.40 387.99
28 CSCO 50 13-Jul-06 18.03 901.50 9-Aug-06 19.40 969.97
29 CSCO 220 13-Jul-06 18.03 3,966.60 14-Aug-06 19.99 4,397.66
30 CSCO 40 3-Aug-06 17.26 690.40 14-Aug-06 19.99 799.58
31 CSCO 250 3-Aug-06 17.26 4,315.00 15-Aug-06 20.74 5,184.84

Applying your formula for New Column L, every single date is replicated.
There ought to be gaps for L25:L27 and for L29.

I propose using COUNTIF from the current row down to the bottom.
If the result is 1, then something lower repeats the date in
the columns we apply the test to; leave this one blank. So far I
haven't gotten this to work, and I'm also not sure about how we'd
add up the data we're accruing for new synthetic totals for Shares
Sold and Weighted Average Price per Share.

(You were right that I wanted a weighted average of share price
paid. I erred in asking for a simple average. Thank you for
catching that.)

Your tip on sorting in two or more steps as needed also works well.
Thank you for that, also!

Obtw: as long as we're making the proposed formulas match my
actual columns -- in the actual sheet, I fill out columns now
all the way to Q. (And it's a work in progress.) What you
show as L, I have as R.

Thanks for all the sweat-effort expended, Ken!

Dallman Ross