# Posts by mljohn

• ## sum if no blanks in matching cells

how do I sum a column of numbers only if the column has a number for every item?
If it does not have a number for every item then it would not sum the column up.

Example

...C ..........D ........E
Item 1 ...\$1.00 ...\$1.00
Item 2 ...\$2.25 ...Blank
Item 3 ...\$1.25 ...\$1.25
Empty ....Blank ....Blank
Empty ....Blank ....Blank
Empty ....Blank ....Blank
Empty ....Blank ....Blank

Total ....\$4.50 .....Blank

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

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

Does anybody have any thought on how to do this?

Thanks

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

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

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

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

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

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

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

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

That would be ok to add another column

• ## 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

• ## VBA code to add letter to following data

My data entry people took short cuts and didn't type out all the parts numbers completely. Here is my problem.

I have a cell with this data:
T610, 612, 612n,614, SB614n, 616

What Visual Basic code would I use to get this result?

T610, T612, T612n,T614, SB614n, SB616

Thanks