Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Excel 2010 - sum total in column based on date

Attached I have a scrubbed version of a spreadsheet I use. What I need is the following when I click a button:

1. Find last month's date in Column Q and where found, when the value in Column J = x, copy the value of Col i and paste that value in Col Y after the last populated row. (So it will end up with a list of rows with different values from col i pasted in Col Y)

2. Find last month's date in Column Q and where found, when the value in Column K = x, copy the value of Col i and paste that value in Col Z starting in the same row as above.

3. Find last month's date in Column Q and where found, when the value in Column L = x, copy the value of Col i and paste that value in Col AA starting in the same row as above.

4. Find last month's date in Column Q and where found, when the value in Column L = gift card, copy the value of Col i and paste that value in Col AB starting in the same row as above.

When a blank value or declined or cancelled or any value other than an 'x' is found in any column (j, k, L) just ignore.

Thank you. Feel free to ask any questions where I'm unclear.
28504371Scrubbed-APPLICANT-STATUS.xls
0
mabehr
Asked:
mabehr
  • 27
  • 25
1 Solution
 
yo_beeDirector of ITCommented:
I am looking at your sheet and I do not see any value for Q, K or X  

Also which Tab are you refering to?

I think you need to give a little bit more data to come up with a solution.
0
 
mabehrAuthor Commented:
Hi yo bee

It is the Applicants tab.

For Col Q it is WHEN there is last month's date in Col Q.

There is values in Col K. A bunch of x's.

Never mentioned Col X but there was the value 'x' in columns.

Does that help?
0
 
mabehrAuthor Commented:
use this one instead please.Scrubbed-APPLICANT-STATUS.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
yo_beeDirector of ITCommented:
Your sheet does not have any Values in Column Q, K , J or x so I am not sure what you are trying to compare here.

If you are trying to find the End of The Month in Q, but there is none it is hard to even start working on a formula.
0
 
mabehrAuthor Commented:
J, K and L have x's but if for some reason you don't see any then go ahead and put some x's in those columns.
And Q gets dates from this month, and past months.

I input dates in this format for Q: 07/31/14

So you could just put the values that I mention above and work from that, can't you?
0
 
yo_beeDirector of ITCommented:
So you want the current months Ending Date in Q or the ending date of Column A
0
 
mabehrAuthor Commented:
Just ignore Col A. That doesn't figure into the formula.

So the idea is that when an applicant is approved I put the date approved in Col Q. And the value I'm looking for is last months date, so if I were to run the function today it would calculate July 2014 dates and only July 2014 dates.
0
 
yo_beeDirector of ITCommented:
So you are putting the Q cell value manually If understand your last reply.
0
 
mabehrAuthor Commented:
Well, not really. When I put a value in Q in automatically (by a formula) inputs today's date. Not sure if that works on your copy but it does on the original.
0
 
yo_beeDirector of ITCommented:
I just put a formula in each of the cells that need a copy of the value from Column I
here is the formula.

=IF(MONTH(EOMONTH($Q2,-1)) - MONTH($Q2)  < 0,IF(J2 = "x",$I2,""),"")
Scrubbed-APPLICANT-STATUS.xls
0
 
mabehrAuthor Commented:
hmmm... I'm not sure what that does or how to initiate the code because what I need the macro to do is
scan Column Q for last month's date and when found, perform the processes described.

It would be best to have a button to click on to initiate the macro.
0
 
yo_beeDirector of ITCommented:
Ok I see what is going on here.

That me see if I can work it up.
0
 
yo_beeDirector of ITCommented:
Here is something I think will work for you

Private Sub ScanColumn()



    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
       ' LastCol = .Cells(.Columns.Count,).End(xlToLeft).Column
        'MsgBox (LastRow & vbNewLine & LastCol)
        
    End With
      
      For i = 2 To LastRow
    
    If (Cells(i, "Q").Value) > 0 Then
    
   Cells(i, "Q").Select
    With Selection
  
    If (DateDiff("M", Now(), ActiveCell.Value)) = -1 Then
    Debug.Print (DateDiff("M", Now(), ActiveCell.Value))
        If Cells(i, "J") = "x" Then
            Cells(i, "Y").Value = Cells(i, "I").Value
        ElseIf Cells(i, "K") = "x" Then
            Cells(i, "Z").Value = Cells(i, "I").Value
        Else: Cells(i, "L") = "x"
            Cells(i, "AA").Value = Cells(i, "I").Value
        End If
    End If
   
    
    End With
End If
        
    Next
      
      

End Sub

Open in new window

0
 
mabehrAuthor Commented:
Thanks, yo bee. I'll have to check this later in the day. I'm out of the office for most of the day, so will test it when I get back.
0
 
yo_beeDirector of ITCommented:
No Problem.

And just to be straight you are only looking to find date values that are from the previous month.

ie. 8/25/2014  is today and the value in the field is 7/28/2014 this would be true where as  8/25/2014 is today and the value in the field is 6/30/2014 this is false.
0
 
mabehrAuthor Commented:
yes.

Using today as an example, any date in July 2014 would be true and all other dates before July 1, 2014 and after July 31,  2014 would be false.
0
 
mabehrAuthor Commented:
For some reason I'm not able to get the button to access or run the code. Please see attached screenshots.

123
0
 
yo_beeDirector of ITCommented:
Change it to a SUB rather than a Private SUB and see what happens
0
 
mabehrAuthor Commented:
Did so and ran but got the attached error.

error.jpgerror1.jpg
0
 
yo_beeDirector of ITCommented:
Can you attach the XLSM?
0
 
mabehrAuthor Commented:
0
 
yo_beeDirector of ITCommented:
Here you go:
The issue was you had other values other than a date value in the Q Cell.
I added some addition IF Then Statements.
Line 19: Was modified to address whether the cell is empty or not
Line 24: Was added to check whether the value is a Date format or not.

Sub ScanColumn()



    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
       ' LastCol = .Cells(.Columns.Count,).End(xlToLeft).Column
        'MsgBox (LastRow & vbNewLine & LastCol)
        
    End With
      
      For i = 2 To LastRow
    
    If Not IsEmpty(Cells(i, "Q").Value) Then  ' <-- I Changed this to check first for Cell Empty state
    
    
   Cells(i, "Q").Select
    With Selection
  If IsDate(ActiveCell.Value) Then  '<-- this then checks if the value is in a Date Format.  
    If (DateDiff("M", Now(), ActiveCell.Value)) = -1 Then
    Debug.Print (DateDiff("M", Now(), ActiveCell.Value))
        If Cells(i, "J") = "x" Then
            Cells(i, "Y").Value = Cells(i, "I").Value
        ElseIf Cells(i, "K") = "x" Then
            Cells(i, "Z").Value = Cells(i, "I").Value
        Else: Cells(i, "L") = "x"
            Cells(i, "AA").Value = Cells(i, "I").Value
        End If
    End If
    End If
    
   
    
    End With
End If
        
    Next
      
      

End Sub

Open in new window

0
 
mabehrAuthor Commented:
thank you and although it ran without error it didn't do anything. It looked like it was scanning through Col Q but I don't see where it put any results. Strange.
0
 
yo_beeDirector of ITCommented:
My Formula only takes the difference of one month back
So if the date in Q is June 2014 or older it will not do anything regarding the copying to the Y, Z or AA cell.


Is that what you want to have done or do you want to have anything <= to Last Month.
So if the date is Mar 2014 or June 2013 and the current Month is Aug 2014 then copy?
0
 
mabehrAuthor Commented:
I only want dates from the previous month for whenever I run it. So for this month the function would look for all dates from and including July 1, 2014 to July 31, 2014.

When we get to September the previous month will be all August 2014 dates. And when we get to October 2014 the previous month will be all dates in September 2014, and so on.
0
 
yo_beeDirector of ITCommented:
Then it is working as it should.
Are you not seeing the values copy over one of the Cells Y,Z or AA?
0
 
mabehrAuthor Commented:
Oh! Now I see them. Thank you. I thought they were going to be consolidated as a block in one area after the last populated row because how am I going to tell the difference for when I run it next month? Not a huge deal though. This might work as well.
0
 
yo_beeDirector of ITCommented:
So you want to have them sorted also?
If so will it be based on Column Q and will it be newest date at the top?
0
 
yo_beeDirector of ITCommented:
Do you want me to add some grouping to the sub?
0
 
mabehrAuthor Commented:
Yes, thank you. It would be best to add some type of grouping so I can see them all in a few consolidated rows rather than spread up and down the spreadsheet. And your suggestion of having the newest date on top based on Q.
0
 
yo_beeDirector of ITCommented:
Here is something, but not sure if it what you want.

Sub ScanColumn()



    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
       LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ''MsgBox (LastRow & vbNewLine & LastCol)
        
    End With
      
      For i = 2 To LastRow
    
    If Not IsEmpty(Cells(i, "Q").Value) Then
    
   Cells(i, "Q").Select
    With Selection
  If IsDate(Cells(i, "Q").Value) Then
    If (DateDiff("M", Now(), ActiveCell.Value)) = -1 Then
    Debug.Print (DateDiff("M", Now(), ActiveCell.Value))
        If Cells(i, "J") = "x" Then
            Cells(i, "Y").Value = Cells(i, "I").Value
        ElseIf Cells(i, "K") = "x" Then
            Cells(i, "Z").Value = Cells(i, "I").Value
        Else: Cells(i, "L") = "x"
            Cells(i, "AA").Value = Cells(i, "I").Value
        End If
    End If
   End If
   
    
    End With
End If
        
    Next
      
ActiveWorkbook.Worksheets("Applicants").Sort.SortFields.Add Key:=Range("Q2") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Applicants").Sort
        .SetRange Range("A2:AA" & lastrow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
 
        
        
End Sub

Open in new window

0
 
mabehrAuthor Commented:
that worked thanks, but could you end the listing on the row where there is the last date from the previous month in Col A? Does that make sense?

If that is too much then this is fine.
0
 
yo_beeDirector of ITCommented:
I am out of the office today so I cannot work on this.

So if I get you all you want is a list of last month from ColA to show?
0
 
mabehrAuthor Commented:
no, I'm just stating where to place the list your code comes up with. How are you determining where the list you create is placed on the spreadsheet?
0
 
yo_beeDirector of ITCommented:
I am just sorting by Column Q descending so x seem to come first then date from newest to oldest.
0
 
mabehrAuthor Commented:
ok, then if we can place that sorted list so that it either ends or starts on the last input date (of the previous month) in Col A.
0
 
yo_beeDirector of ITCommented:
I think the only way to do this is to create a column that gets populated with the End of Last Month date based on the Q column date.

This can be done pretty simply by creating a new column with a row formula that populates.
Here is the formula: =IFERROR(EOMONTH($Q2,-1),0) then you can modify the VBA to Sort by the new column.
Change Q2 to whatever the new column is AC2 or something like that.
0
 
mabehrAuthor Commented:
Okay, will give it a try. Not sure how I implement the formula you have above. Do I just paste it into Column AC and copy down?
0
 
yo_beeDirector of ITCommented:
Yep and change the column format to Short Date.
0
 
yo_beeDirector of ITCommented:
where you able to get the desired results with my suggestions?
0
 
mabehrAuthor Commented:
sorry, yo bee. haven't had time to try it yet. hopefully will today or tomorrow. thanks.
0
 
yo_beeDirector of ITCommented:
Let me know if it works or need further assistance
0
 
mabehrAuthor Commented:
yo bee, not sure how to implement this.

why would put the formula is AC2 when the the columns needing this sorting are Col y, z and AA?
0
 
yo_beeDirector of ITCommented:
From the example of the data you posted I did not see that the Column A Date landed in the Previous month date for Column Q.

So to work around this I created a calculated column in AC to populate the cell with last month last day of the end of the month so the items can be sorted with the items that were approved Newest to oldest.

That is the impression that I got that you wanted to do.
0
 
mabehrAuthor Commented:
is it possible you could use the spreadsheet I sent, yo bee, and populate the cell with the code you want me to insert? I'm not following you on this one, thus the delay.
0
 
yo_beeDirector of ITCommented:
Here it what I am talking about:
Look at column AC

Scrubbed-APPLICANT-STATUS-Aug-28-14.xlsm
0
 
mabehrAuthor Commented:
ok, I'll try that out when I get home tonight. Thanks for your help.
0
 
yo_beeDirector of ITCommented:
Does that make sense what I am doing there?
0
 
mabehrAuthor Commented:
Yes, I see the results but I don't think that helps. It creates a lot of dates that I then would have to sort through to find last months dates. I'd rather just see the last months only that have to hunt through a list.

I think I'll just take the previous solution you provided as that will be sufficient.
0
 
yo_beeDirector of ITCommented:
The macro sorts column AC newest to oldest and the field is populated based on Column Q date - 1 month.  
So technically the newest month in Column AC would be last months end of months date.

I will be more than happy to continue to assist, but if you feel that I am going down the wrong road please let me know
0
 
mabehrAuthor Commented:
Thank you, yo-bee. Works just as I need. Your help has been much appreciated.
0
 
yo_beeDirector of ITCommented:
Glad I was able to assist you with this.
You are more than welcome to send me a private message if you need further assistance with this.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 27
  • 25
Tackle projects and never again get stuck behind a technical roadblock.
Join Now