find non null in column and move it to first row?

I also posted this at http://www.mrexcel.com/forum/e…olumn-move-first-row.html

I have a large list of customer activity. Column A-K
Each customers activity is separated by two blank rows.
Each customer has varying number of rows for their activity.
They are sorted by customer and most recent date.
In column "M" of the first row of each customer I show the latest date.
In column "L" I have pulled the subtotaled amount of purchases on the last row of each customer.

I want to show the subtotaled amount of purchases in Column "L" on the first row of each customer, next to the date in column "M"

How do I do this?

For the date I used an If statement and the blank row above the first row but with the subtotal I can't do that because every customer has different number of rows for their activity history.

Thanks

Matt

Re: find non null in column and move it to first row?

That would be ok to add another column

Re: find non null in column and move it to first row?

I tried this and all I got was #NUM!

Solved that by double clicking on the formula and doing Control+Shift+Enter

also was getting a Ref! error on the really large histories but solved that by changing the formula to ...
3="","",INDEX($L$2:$L150,SMALL(IF($L$2:$L$6177<>"",ROW($L$2:$L$6177)-ROW($L$2)+1),SUMPRODUCT(--(LEN($M$2:M3)>0)))))

I increased the last number in "INDEX($L$2:$L150".

I don't know why this worked.

Thanks, you're great!

Matt

Re: find non null in column and move it to first row?

Robert can you explain how this formula is working?

How would I apply this formula to show the number of years that each customer has been with me with the results in column "O" in the first row?

How would it change and why.

Thanks

Re: find non null in column and move it to first row?

forum.ozgrid.com/index.php?attachment/55796/

Basically I want to show how long they were or have been my customers. I guess that would take two columns? I don't think 4.3 means 4 years and 3 months.

Unsure how to best do this so I can sort them by length of term.

Thanks

Re: find non null in column and move it to first row?

Does anybody have any thought on how to do this?

Thanks