Excel Coding

[schnabs]

I have some excel files I need to write some code for. This is a monthly process, so I'll need to do it frequently. This is how my data is setup.


ColumnA ColumnB ColumnC ColumnD
Balance
Row 1
Row 2 John
Row 3 400 11/27/08
Row 4 500 12/19/08
Row 5 0 12/30/08
Row 6 250 1/29/09
Row 7 300 2/1/09
Row 8 0 2/10/09
Row 9 225 2/15/09
Row 10 Total 225
Repeat

I have 90 files, with anywhere between 100-1000 customers in each, set up as such. Now as you can see, the sample I am showing has had a zero balance multiple times throughout the year. I only need the most current activity from his last zero balance. So all rows 3-8 need to be deleted. I still need the customer, and charges after their most recent zero balance. Thanks in advance.

 

[SkipVought]

Hi,

How is this REPORT generated?

Can you get to the source data that generated this REPORT?

Can you have the REPORT changed?

I would move heaven and earth in order to get real tabluar data rather than a REPORT!!! (and I do!!!)



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

Haha. I only wish. I posted on hear several weeks or so back. I use Quickbooks to generate the report, and I am currently working to modify the data, but it is looking less and less likely. If you have any suggestions on modification, I can look to see if they might work.
Thanks

 

[Bong]

I think you've complicated it slightly by having "Total" in the same column as your customer name. This could be bad if you ever have a customer named, "Total". Anyway, what I'd do is have 2 variables for the rows "from" and "to" to delete. Set your "to" row variable to "0". Then I'd march down the rows until Column A is not blank AND not "Total". Then set your "from" row variable to the next row number. Continue down until either column A has "Total" or Column B has zero. If the former, check if your "to" row variable is still "0". If it is, do nothing. If not delete "from" to "to". If the latter, set the "to" row variable to the current row number and continue.

_________________
Bob Rashkin

 

[schnabs]

Thanks Bong. To be honest with you, "Anyway, what I'd do is have 2 variables for the rows "from" and "to" to delete" that part confuses me a bit. Could you give an actual example, using real row/column numbers.

 

[SkipVought]

here are two formulas that will get you where you need to be...
[tt]
D2: =IF(ISBLANK(A2),D1,IF(A2<>"Total",A2,D1))
E2: ==IF(AND(B1=0,SUMPRODUCT(($D$22=D2)*($B$2:B2=0))=SUMPRODUCT((D$2$999=D2)*(B$2:B$999=0))),C1,0)
[/tt]
assuming that the last ROW is 999 and that the heading, Balance is in ROW 1.

Column D is the Name Key.

The result in column E is that every non zero value is the date for the last balance you specified. AutoFilter on <>0.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

Or, brute force code:

Sub balance()
    r1 = Sheet1.UsedRange.Rows(1).Row + 1
    r2 = Sheet1.UsedRange.Rows.Count
    intFrom = 0
    intTo = 0
    For i = r1 To r2
        strNm = Cells(i, 1).Value
        If strNm <> "" And strNm <> "Total" Then
            intFrom = i + 1
        ElseIf Cells(i, 2).Value = 0 Then
            intTo = i
        End If
        If strNm = "Total" And intFrom < intTo Then
            For j = intFrom To intTo
                Cells(j, 1) = "delete"
            Next
            intTo = 0
        End If
    Next
    'it's best to delete from the bottom up
    For i = r2 To r1 Step -1
        If Cells(i, 1).Value = "delete" Then Sheet1.Rows(i).Delete
    Next
End Sub

_________________
Bob Rashkin

 

[schnabs]

Thanks guys I REALLY appreciate it. I don't have time now to run the code through, but I'll let you know how it goes when I do. Thanks again!

 

[schnabs]

Skip,
Two things. My columns are actually different than my example. I just tried to simplify it as much as possible. My Balance is in I, and my date is in E. I changed your code to accommodate as such, but it didn't work. Excel said there was an error, and changed it. But when it's changed, it does not work. Would the different columns make a difference?
Thanks.

 

[AnotherHiggins]

What was the error?
For which formula?
What did you change the formula to?
Please paste it here so we can see it.

Just to take a SWAG at it, I'll guess that you tried to reference entire columns in the SumProduct function, which won't work.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[schnabs]

The top is what I modified it to, and the bottom is the original.

==IF(AND(I1=0,SUMPRODUCT(($J$2:J2=J2)*($I$2:I2=0))=SUMPRODUCT((J$2:J$5000=J2)*(I$2:I$5000=0))),E1,0)

==IF(AND(B1=0,SUMPRODUCT(($D$22=D2)*($B$2:B2=0))=SUMPRODUCT((D$2$999=D2)*(B$2:B$999=0))),C1,0)

And this is what excel changed it to. Unless I'm mistaken, is just removed one of the two = at the beginning.

=IF(AND(I1=0,SUMPRODUCT(($J$2:J2=J2)*($I$2:I2=0))=SUMPRODUCT((J$2:J$5000=J2)*(I$2:I$5000=0))),E1,0)

 

[AnotherHiggins]

The double "=" was just a typo.

What is it doing that is unexpected? If you click into the formula bar at the top, you will see the cells that are being referenced in different colors. Notice that some cell references are absolute references and some are relative references. So this formula must be entered on the right row.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[schnabs]

Well from what Skip said, the formula should only populate the column I run the formula in with zeros in the proper places. Instead I get zeros down the entire sheet. I placed the formula in the row according to skips instructions. Like I said, it's in column K instead of E, but it's on row 2.

 

[AnotherHiggins]

Well from what Skip said, the formula should only populate the column I run the formula in with zeros in the proper places.

Actually, Skip said,

The result in column E is that every non zero value is the date for the last balance you specified. AutoFilter on <>0.

(emphasis added)

So it should be zeros all the way down, except for Row 9 (in the example), which will have a date in it. Note that the date will appear as a serial number (today is 39864).

Your formulas look right, are you getting a non-zero value for row 9?



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[schnabs]

No. I get a 0 in every cell, all the way down.

 

[AnotherHiggins]

If you data looks like this:
[tt]
Col E Col F Col G Col H Col I
Row 1___________|_______|_______|_______Balance_
Row 2___________|_______|_______|_______John____
Row 3___11/27/08|_______|_______|_______400_____
Row 4___12/19/08|_______|_______|_______500_____
Row 5___12/30/08|_______|_______|_______0_______
Row 6___1/29/09_|_______|_______|_______250_____
Row 7___2/1/09__|_______|_______|_______300_____
Row 8___2/10/09_|_______|_______|_______0_______
Row 9___2/15/09_|_______|_______|_______225_____
[/tt]
Then in J2 you should have:
[tab]=IF(ISBLANK(H2),J1,IF(H2<>"Total",H2,J1))
and in K2 you should have:
[tab]=IF(AND(I1=0,SUMPRODUCT(($J$2:J2=J2) * ($I$2:I2=0)) = SUMPRODUCT((J$2:J$999=J2) * (I$2:I$999=0))),E1,0)

Then drag each formula down the column.

You should end up with:
[tt]
Col E Col F Col G Col H Col I Col J Col K
Row 1___________|_______|_______|_______Balance_|_______|_
Row 2___________|_______|_______|_______John____John____0
Row 3___11/27/08|_______|_______|_______400_____John____0
Row 4___12/19/08|_______|_______|_______500_____John____0
Row 5___12/30/08|_______|_______|_______0_______John____0
Row 6___1/29/09_|_______|_______|_______250_____John____0
Row 7___2/1/09__|_______|_______|_______300_____John____0
Row 8___2/10/09_|_______|_______|_______0_______John____0
Row 9___2/15/09_|_______|_______|_______225_____John____39854
[/tt]
Now that I look at it, you might want to change E1 to E2 to report the last date that there was a non-zero balance. But other than that, I just recreated your spreadsheet as seen above and the formulas work for me.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[schnabs]

Ok, well out of curioustiy, I re-created my example in Excel and ran the code. It worked fine. Apparently something is off with the way I used it on my sheet. I noticed that on my real sheet, the name key doesn't populate in the same way that it did in my example, so I'll start there. I'll keep you posted.

 

[schnabs]

I got it partially working. Column A, where it was blank, was not reading as blank. I went though and deleted the already "blank" rows, and that populated the names correctly. The problem I have now, is that Row 2, with the name, needs to not be deleted, as it has the original customer name. The total column is also showing up with a 0, which I also cannot delete.
Thanks

 

[SkipVought]

Then that's when the name in one cell <> name in the previous cell. Pretty simple stuff.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

Skip,
I was wondering if I could upload or send you my main file so you can look at it??