# [SOLVED] Count cell changes in excel based on another cells value



## rywags11 (May 23, 2012)

Hello

I have a workbook which contains a production schedule in a tab titled "Schedule". This tab is constantly updated and I need to count (on going) the number of times the content of Column B, C, and D changes based on the value in column A. 

Column A is for the "work order" and the value in this column will always be a unique value. When the work order has been processed, the line containing that work order & all its associated criteria (in columns B, C, and D) are deleted.

I need to put something in the tab titled "Log" that automatically populates new work orders that are entered in the "Schedule" tab and then counts the number of times the value associated with that work order in column B (start date), column C (start time), and column D (SKU) change.

I need help writing the formula to count changes in B, C, and D, but I honestly have no idea how to automatically populate new work orders in the Log & tie the changes in columns B, C, and D to that work order. 

A sample of my file is attached for reference. Any help anyone can give will be greatfully appreciated!


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*

Logging changes of this kind reliably is problematic. The main issue I see is that a person might change something, then almost immediately change it back (eg because the wrong row was selected or a wrong value was input). With what you've proposed, that'll be logged as two changes, and you'll have no way of knowing later on that this is what happened.

What you can do, though, is to log the details of every change so they can be reviewed later on and tallied/excluded as appropriate. The attached takes this approach, with the date & time of the change in YYYYMMDDHHMMSS format.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

Ok, I definitely see the logic in that and the flaw in my original plan, and I think that code is almost exactly what I was looking for.

One added thing that would improve the functionality for what we're looking at is having it tell me what the value was before the change. This would work better to serve the objective because when a work order is completed, it will be deleted from the "Schedule" tab, and I have no control over that... so to really get what we need from it, we need to know what it was before the change so we can analyze the reason.

As I was writing that, I was thinking that since the "Schedule" tab is going to be constantly changed by deleting comlpeted work order lines & adding new ones, the Log will end up tracking that as well... and that isnt what we're looking at.

In a nutshell, the way scheduling is done currently isn't very effective, especially because it's handled through an excel file like this... but we are trying to develop metrics for how often a scheudled work order is changed within 72 hours before the work order actually runs. I still like what the code is doing, but can you tell me how I could modify it to log just the instances of change when rows B, C, and D are changed after the initial order entry and excluding when the order is deleted... and showing what the original value was compared to the updated value? That would pretty much suit the purpose perfectly.

Sorry I didnt just spit all of that out in the original question... I guess I hadn't really thought it through all the way.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

P.S. I'm newer to this forum, so can you tell me how I can give you some accolades for the great help?


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*



rywags11 said:


> One added thing that would improve the functionality for what we're looking at is having it tell me what the value was before the change. This would work better to serve the objective because when a work order is completed, it will be deleted from the "Schedule" tab, and I have no control over that... so to really get what we need from it, we need to know what it was before the change so we can analyze the reason.


The 'before' values are recorded - they're on whatever the previous row for the same item was. The only one you might miss (depending on how your initialize the worksheet) is the very first line.



> I still like what the code is doing, but can you tell me how I could modify it to log just the instances of change when rows B, C, and D are changed after the initial order entry and excluding when the order is deleted... and showing what the original value was compared to the updated value? That would pretty much suit the purpose perfectly.


Only Changes to columns B are tracked already. As for what happends when you clear the data sheet, simply delete all the records on the log sheet and save the workbook beforehand.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

You're right, didnt think about the fact that the before values are recording. Can you tell me how to record the after values? I modified the "Log" tab to have the before and after columns in the new attachment. I know this breaks the code, but if you could tell me the string for how to record the after values, I can modify the code to make it work. 

I dont want to lose the information in the Log sheet, so I wouldnt want to delete it... but I dont want it to record multiple instances of irrelevant information. For example, when I put a new batch into the Schedule tab, its recording on one line the date value I input, then on a seperate line its recording when I put the time value, and then a 3rd line for the SKU value. This is exemplified in the new attachment as well. So its recording me setting up a new work order, which isnt the information thats needed... so it would just be extra to have to sift through. Is there any way to modify it so its ignoring a new work order value in the Schedule sheet until that line/work order its fully set up (like, once column D goes from null to not null), and then start Logging it?


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*

The last row for each value _is _the 'after' value.

If you paste the initializing data for a new batch as a block, you'll get one entry for the block, rather than one per column. Alternatively, you can always edit the log sheet by simply deleting the first couple of rows for the new entry, then immediately saving the changes. If you look closely, you'll see that, as you add a new entry, each successive line replicates the data you've already input.

I could add more code to not log incomplete rows, but then data deletions (for whatever reason) that should get logged might be missed.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

Dang it! I miscommunicated that terribly!! I meant can you tell me how to record the BEFORE values?

I see how the code is working, but I would be ok with missing some of the incomplete row data points to avoid duplications. Could you add the extra coding?


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*

Both the 'before' and 'after' values are _both _there already. For any row, the last entry is the most recent 'after' value. Whatever row precedes it for the same work order is the 'before' value. It's really quite simple.

The attached update shouldn't log entries with empty values in any column.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

Ok, I think I'm getting it now... so tell me if I'm wrong here:

-Work order gets entered in Scheduling tab
---->Order does not get logged until all values through column D are entered
-Once work order if fully set up, it gets logged
-Every time something changes for any work order, all values from A-D in the Schedule sheet get recorded
---->The log can be reviewed to see the full logged history for each work order by reviewing each line that # appears on in the Log sheet

Does that all sound right? Cuz if it does, then I get it now AND it's perfect!

If I set an event to sort the Log upon worksheet open (to make it easier to review by sorting work order # appearances in numberial order), will that harm any of the functionality?


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*

Yes, your understandings of how it works are correct and, no, I don't expect that sorting the log data will do any harm.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

So, I dont mean to kick a dead horse with this, but a new piece of information has popped up and I hit a wall again :banghead:.

The way this file apparently works (which I didnt know before) is that there is a hidden "Priority" column which gets updated daily. So basically, this column is used to shuffle around the work orders based upon decisions made on the fly. That wouldn't be a problem, except that whenever the "Priority" number is changed, the whole thing gets sorted in ascending order of priority... which causes the issue of creating a ton of Log entries, which is related to my previous desire to prevent irrelevant data from being recorded.

If you have the patience to help with this matter a little more, can you tell me how I can change the code so that when the data is sorted, it doesnt get recorded? I tried to put something in there to make it look at only when cell values change and not just positioning, but I pretty much broke the whole system and had to retrieve a back up :facepalm:.

Thanks again for all of your help before, and any you can give now!


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*

Where, exactly, is this hidden "Priority" column (ie which worksheet and column?) I added such as column as Column E on the schedule sheet, made a couple of priority entries against the existing data (no logging occurs), then sorted the schedule sheet and the sort wasn't logged on the log sheet.


----------



## rywags11 (May 23, 2012)

*Re: Count cell changes in excel based on another cells value*

You're absolutely right. I'm sorry about that... I just got really confused when I was tracking things in the Log.

I suppose it wont hurt to ask one more dumb question. Is there a way to track changes to the values only if the changes were made within 72 hours of the start of the work order?

For example, if the work order was entered into the system on 06/20/12 to start on 06/28/12, then any changes made on or before 06/25/12 are ok and dont need to be tracked... but all changes made after 06/25/12 should be tracked.

If you could help me jump this last hurdle, I would appreciate it, and promise to leave this as the last request for help (on this issue at least)!


----------



## macropod (Apr 11, 2008)

*Re: Count cell changes in excel based on another cells value*

Your latest specs would be difficult to implement.

Whenever someone makes the start date/time later, you could end up with logged changes that pre-date the ones that should be logged. For example, if the work order was entered into the system on 06/20/12 to start on 06/28/12, then made a number of changes on 06/25/12 and 06/26/12, before changing the start date to 06/30/12, the changes on 06/25/12 and 06/26/12 will still be in the log.

Conversely, whenever someone makes the start date/time earlier, you could end up missing the required logs. For example, if the work order was entered into the system on 06/20/12 to start on 06/28/12, then made a number of changes on 06/23/12 and 06/24/12, before changing the start date to 06/25/12, there would be no record of the changes on 06/23/12 and 06/24/12 that, with the benefit of hindsight, should have been logged.

The result will be a log that has a confusing mix of excess and missing records.

The only safe option is to delete unwanted rows from the log at some point after the project has actually started.


----------



## rywags11 (May 23, 2012)

Definitely some great points in there. I agree with you that this is just going to have to be the extent of the automation and we're just going to have to bite it and review the details :smile:.

Thanks again for all of your help. You've been absolutely fantastic and I sincerely appreciate the opportunity to have learned so much!


----------



## rywags11 (May 23, 2012)

Hi there macropod

Not sure if this topic is past its statute of limitations, but the people working with me on this data collection piece have asked for the timer... and try as I might I cant work it out. 

Is there any chance you could help me again with the code for the 72 hours? The problem is that the Log has recorded 200 entries in the last 18 hours, but there havent been nearly that many changes. I'm hoping that putting it on a timer will reduce the instances to a manageable level, so that we can figure out whats causing it to record so many instances.


----------



## macropod (Apr 11, 2008)

How about we try to isolate the cause of the problem first? It may be that a particular user is doing something dumb (eg copying & pasting data without changing it) and needs 'training' (a euphemism for wrists slashed).

You can track the user details by adding a new line to the macro:
.Cells(LRow, 6).Value = Environ("Username")
Once you've identified which user is responsible for the plethora of logged entries, you can soon find out what they're doing that causes them.


----------



## rywags11 (May 23, 2012)

Ok, I see where you're headed and it makes great sense! I'll throw the code in today and see what I come up with in a day or two.


----------



## rywags11 (May 23, 2012)

Ok, I've been trying my hardest to figure this problem out on my own because I dont want to keep asking for help, but no matter what i've done in the last few weeks to fix it , I keep getting a compile error that says "Cant find project or library" and it highlights the "Environ" word in the code. 

I had to modify the code to fit the way the department uses their file, so it looks like this:


```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long, TRow As Long
If Not Intersect(Target, Range("C:J")) Is Nothing Then
  TRow = Target.Row
  With Worksheets("Log")
    If Cells(TRow, 3).Value <> vbNullString And _
      Cells(TRow, 5).Value <> vbNullString And Cells(TRow, 7).Value <> vbNullString And _
      Cells(TRow, 8).Value <> vbNullString And Cells(TRow, 9).Value <> vbNullString Then
      LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
      .Cells(LRow, 1).Value = Cells(TRow, 3).Value
      .Cells(LRow, 2).Value = Cells(TRow, 4).Value
      .Cells(LRow, 3).Value = Cells(TRow, 5).Value
      .Cells(LRow, 4).Value = Cells(TRow, 6).Value
      .Cells(LRow, 5).Value = Cells(TRow, 7).Value
      .Cells(LRow, 6).Value = Cells(TRow, 8).Value
      .Cells(LRow, 7).Value = Cells(TRow, 9).Value
      .Cells(LRow, 8).Value = Cells(TRow, 10).Value
      .Cells(LRow, 9).Value = Now()
      .Cells(LRow, 10).Value = Environ("Username")
    End If
  End With
End If
End Sub
```
Its set to activate on worksheet change. 

The whole thing works as it should when I take out the username line (.Cells(LRow, 10).Value = Environ("Username")), but when I put it back in, it breaks down.

The major issue I'm having is that the file that was created wasnt built to accomidate changes/updates/sanity. So whenever I put something new in (like the username coding), it stops working. It took me days to figure out how to get the OTHER coding to work (the orignal code for logging) because it didnt like that I was defining things... because nothing else in the VBA was defined properly. 

I'm done ranting about my plight. The log is logging more than a few hundred "changes" every day that really arent true changes (again, because of the way the file was built & is used), but I do believe that its being caused by only one or two individuals... so anything you can do to help me revise the username coding so that I can make it work will be hugely appreciated so that I can finally hand the data collection tool over to our sponsor. 

Thanks again for all of your help and patience!


----------



## macropod (Apr 11, 2008)

It's a bit of a worry if 'Environ("Username")' generates an error. That suggests something's wrong with your Excel setup. The first thing to check in the vbe, under Tools|References, is that the following options are all checked:
• Visual Basic for Applications
• Microsoft Excel # Object Library
• OLE Automation
• Microsoft Office # Object Library
where # is the version number.

As I don't have a copy of your current workbook structure, I'm not sure of what the implications are for the coding changes you've made (though I can see that you're evidently capturing more data now). Aside from that, have a play with the attached. The revised macro should eliminate 'no change' logs.


----------



## rywags11 (May 23, 2012)

For the complex "If" statement, I modified it to fit the way I had to design the log (because I cant change the design of the tab with the schedule due to forces outside of my control)... which is similar to the last code I pasted in. Can you help me understand how the "If" statement is structured so that I can wrap my head around how it works & then modify it to fit into the design?

It looks like this now:

```
If Cells(TRow, 3).Value <> vbNullString And Cells(TRow, 5).Value <> 0 And _
        Cells(TRow, 7).Value <> 0 And Cells(TRow, 8).Value <> 0 And Cells(TRow, 9).Value <> vbNullString Then
      LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
      For i = 2 To LRow
        If .Cells(i, 3).Value = Cells(TRow, 1).Value Then
          If .Cells(i, 5).Value = Cells(TRow, 2).Value Then
            If .Cells(i, 7).Value = Cells(TRow, 3).Value Then
              If .Cells(i, 9).Value = Cells(TRow, 4).Value Then
                bUpd = False
                Exit For
              End If
            End If
          End If
        End If
```


----------



## macropod (Apr 11, 2008)

Without seeing your workbook, I'm really only guessing here, but I think you need something like the following (note the changes to the If tests from your post, in particular):

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long, TRow As Long, bUpd As Boolean
If Not Intersect(Target, Range("C:J")) Is Nothing Then
  TRow = Target.Row: bUpd = True
  With Worksheets("Log")
    If Cells(TRow, 3).Value <> vbNullString And _
      Cells(TRow, 5).Value <> vbNullString And Cells(TRow, 7).Value <> vbNullString And _
      Cells(TRow, 8).Value <> vbNullString And Cells(TRow, 9).Value <> vbNullString Then
      LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
      For i = 2 To LRow
        If .Cells(i, 1).Value = Cells(TRow, 3).Value Then
          If .Cells(i, 3).Value = Cells(TRow, 5).Value Then
            If .Cells(i, 5).Value = Cells(TRow, 7).Value Then
              If .Cells(i, 7).Value = Cells(TRow, 9).Value Then
                bUpd = False
                Exit For
              End If
            End If
          End If
        End If
      Next
      If bUpd = True Then
        .Cells(LRow, 1).Value = Cells(TRow, 3).Value
        .Cells(LRow, 2).Value = Cells(TRow, 4).Value
        .Cells(LRow, 3).Value = Cells(TRow, 5).Value
        .Cells(LRow, 4).Value = Cells(TRow, 6).Value
        .Cells(LRow, 5).Value = Cells(TRow, 7).Value
        .Cells(LRow, 6).Value = Cells(TRow, 8).Value
        .Cells(LRow, 7).Value = Cells(TRow, 9).Value
        .Cells(LRow, 8).Value = Cells(TRow, 10).Value
        .Cells(LRow, 9).Value = Now()
        .Cells(LRow, 10).Value = Environ("Username")
      End If
    End If
  End With
End If
End Sub
```
I also note that you seem to be logging a lot more now. In that case, you really should extend the initial logging tests to cover the additional content and, similarly, the nested if tests to cover those items as well.


----------



## rywags11 (May 23, 2012)

I think we're going to keep banging our heads on the wall with the limitations of my explanations... so I'm attaching the file. 

To open it, select "Yes" on the 1st splash screen, then choose "Barnett, Kevin" as the user and enter password as "planner1". Once in, the whole workbook should be unprotected, but to access the VBA code you will need to enter "planning". 

The way the file is currently used, the "Mix Schedule" tab is modified mainly by the "Priority" column. The way its set up is that when the priority # is changed, the clump of lines that are all related by the "X" column resorts itself so that the "Priority" column is in numerical order. Usually, they will enter a new line at the bottom and then when they decide what priority they want to make it, they change the priorities of the other related items in "X" and then click the "True Sort" button at the top, which will then sort the new line into the correct order as it relates to the clump of items in "X". 

As you can see in the Log, multiple lines are logging at the same time... which is the problem. Even if we couldnt get the multiple lines to stop recording because of the way this horrendous file was originally created, we could live with that if we could at LEAST be able to identify wha the true change was, so that we can apply a reason for the change (which is the whole purpose of this data collection).

I appreciate all the help you've given and hope that this helps so we can put it to bed!


----------



## macropod (Apr 11, 2008)

Try the following:

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long, TRow As Long, i As Long
If Not Intersect(Target, Range("C:J")) Is Nothing Then
  TRow = Target.Row: bUpd = True
  With Worksheets("Log")
    'Test whether columns C:I all have data. If not, don't log.
    For i = 3 To 9
      If .Cells(TRow, 3).Value = vbNullString Then Exit Sub
    Next
    'Columns C:I have data, so test whether the data are new or have changed.
    'If already logged, don't log again.
    LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
    For i = 2 To LRow - 1
      If .Cells(i, 1).Value = Cells(TRow, 3).Value Then
        If .Cells(i, 2).Value = Cells(TRow, 4).Value Then
          If .Cells(i, 3).Value = Cells(TRow, 5).Value Then
            If .Cells(i, 4).Value = Cells(TRow, 6).Value Then
              If .Cells(i, 5).Value = Cells(TRow, 7).Value Then
                If .Cells(i, 6).Value = Cells(TRow, 8).Value Then
                  If .Cells(i, 7).Value = Cells(TRow, 9).Value Then
                    If .Cells(i, 8).Value = Cells(TRow, 10).Value Then
                      Exit Sub
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      End If
    Next
    'This record NOT already logged, so log it.
    For i = 1 To 8
      .Cells(LRow, i).Value = Cells(TRow, i + 2).Value
    Next
    .Cells(LRow, 9).Value = Now()
    .Cells(LRow, 10).Value = Environ("Username")
  End With
End If
End Sub
```


----------

