Anatomy of a Stackoverflow Discussion, Part II

Introduction

There’s a reason I named this blog the way I did. The first post is a good example of how a dummy does it. Turns out I did the opposite of what the asker needed. He wanted to find non-matching pairs, and I gave him matching pairs. So I don’t reexplain from scratch here, but I’m going to give an updated macro and explain the differences.

Scope of Application

This post references Microsoft Excel 2013 running on a Windows 8.1 machine. I’ll also be referencing Stackoverflow as experienced through Firefox 28.0 running in Sandboxie 4.08.

User Requirements

This one’s on me. I read the question once, started looking at the sample data, then forgot what the ultimate goal was because I was caught up in the minutiae of making things work. Granted, this was a casual response to an online question, but this is about par for the course in programming. Sometimes the requirements change, many times they were ill-defined in the first place, you have to constantly be on the lookout for scope creep, and other times, it turns out that I’m just kind of a dummy. So here’s a macro that does what the user actually asked for:

Sub TakeThree()

Dim rowCount1 As Long
Dim rowCount2 As Long

rowCount1 = ThisWorkbook.Sheets(1).Range("B20").SpecialCells(xlCellTypeLastCell).Row
rowCount2 = ThisWorkbook.Sheets(2).Range("C2").SpecialCells(xlCellTypeLastCell).Row

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = ThisWorkbook.Sheets(1).Range("B20:B" & rowCount1)
Set rng2 = ThisWorkbook.Sheets(2).Range("C2:C" & rowCount2)

Dim currentRow As Long
currentRow = 2

For Each cell In rng1.Cells
For Each cell2 In rng2.Cells
If cell2.Value = cell.Value And cell2.Offset(0, -1).Value = cell.Offset(0, 5).Value And cell2.Offset(0, -2).Value = cell.Offset(0, 2).Value Then
GoTo NextIteration
End If
Next cell2
ThisWorkbook.Sheets(1).Rows(cell.Row).Copy Destination:=ThisWorkbook.Sheets(3).Range("A" & currentRow)
currentRow = currentRow + 1
NextIteration:
Next cell

End Sub

What’s the Difference?

We really haven’t changed much. We just moved the lines that copy and paste the row from sheet 1 and the currentRow increment outside of the loop through sheet 2. Why does that make it work? As I’ve said before (I think), the biggest part of coding isn’t so much about knowing how to read and write the language, it’s knowing how to work the logic.

Let’s frame the requirements one more time to help us understand how we got here. The asker wanted to compare data in 3 cells on sheet 1 to data in 3 cells on sheet 2. If the cells on sheet 1 do not match any of the corresponding cells in sheet 2, they should be copied to sheet 3. To be sure that each set of 3 cells from sheet 1 doesn’t match any of the corresponding cells in sheet 2, our only course of action is to compare each set of cells on sheet 1 to every set of cells on sheet 2–unless we get a match. As soon as the cells on sheet 1 match some cells on sheet 2, we don’t have to keep going with the comparison and can move to the next set of cells on sheet 1. But unless we get a match, we can’t copy anything from sheet 1 to sheet 3 until we’re sure we’ve exhausted every option.

So now look at what the loops are doing. The first one, “For Each cell in rng1.Cells,” is going to loop through every cell in sheet 1, as we discussed. It immediately starts another loop that will check the values at this spot in sheet 1 against a set of cells in sheet 2. If the cells from sheet 2 match the cells from sheet 1, the code jumps to the next iteration–completely bypassing the step that copies a row from sheet 1 to sheet 3.  However, if the code gets through every single set of cells in sheet 2 without finding a match, then it leaves that loop. There waiting on the other side is the code that says, “Oh. Hey. Couldn’t find a match on the other sheet, and now you want me to give you a ride to sheet 3? I guess that’s cool.”

Hopefully that’s clear enough. Cuz that’s all I’ve got.

Love and kisses,

Tyler

Advertisements

7 thoughts on “Anatomy of a Stackoverflow Discussion, Part II

  1. I really like your code. I have been working to reverse the logic (find a row that matches the criteria), change the action (copy cells from sheet 1 match to sheet 2 match) and expand the criteria to 5 or 6 cells must match. Was hoping you might have a bit of time to help me work out the bugs.

  2. EDIT

    Seems like this modification does the trick. Adjusted the ranges to my requirements. Added a few more criteria to check for. Eliminated the print to sheet(3) and moved the action to follow a positive match. In this case after a match is found between the two sheets I update the PM sheet row with data from the notes sheet row matched against. Added I and j to simplify the syntax of action.

    I already have working macros to allow users to input note and scheduling data that gets saved back to the raw data sheet (PM) accurately. A weekly update to the raw data causes a filter and save routine to run that filters the old raw data for any rows with note and/or scheduling information in the row. This creates the notes sheet. After the new (updated) raw data is imported into the workbook the below code is ran to see if the new data has any rows that match against the saved notes/dates. This allows me to save working notes throughout a two month work cycle where the raw data is being updated every week.

    Thanks Tyler. Been looking for a solution to bring the notes back into the updated raw data for over a week.

    I will be testing this for a bit. If you see any flaws let me know.

    May try the syntax “If i.value=j.Offset(0,1).value” etc just to compact the code a bit.

    Sub PTest()

    Dim rowCount1 As Long
    Dim rowCount2 As Long
    Dim i As Long
    Dim j As Long

    rowCount1 = ThisWorkbook.Sheets(“PM”).Range(“A3”).SpecialCells(xlCellTypeLastCell).Row
    rowCount2 = ThisWorkbook.Sheets(“notes”).Range(“A6”).SpecialCells(xlCellTypeLastCell).Row

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng2 = ThisWorkbook.Sheets(“PM”).Range(“A3:A” & rowCount1)
    Set rng1 = ThisWorkbook.Sheets(“notes”).Range(“A6:A” & rowCount2)

    Dim sheet1() As Variant
    ReDim sheet1(rowCount1 – 1, 4) ‘ 2 means 3 for 0,1,2 elements in array Change to 5 for 0-6

    Dim n As Long
    n = 0

    For Each cell In rng1.Cells
    For Each cell2 In rng2.Cells
    i = cell.Row
    j = cell2.Row
    If cell2.Value = cell.Value And cell2.Offset(0, 1).Value = cell.Offset(0, 1).Value And cell2.Offset(0, 4).Value = cell.Offset(0, 4).Value And cell2.Offset(0, 6).Value = cell.Offset(0, 6).Value And cell2.Offset(0, 7).Value = cell.Offset(0, 7).Value Then
    Worksheets(“PM”).Cells(j, 31) = Worksheets(“notes”).Cells(i, 31)
    Worksheets(“PM”).Cells(j, 32) = Worksheets(“notes”).Cells(i, 32)
    GoTo NextIteration
    End If
    Next cell2
    ‘ThisWorkbook.Sheets(1).Rows(cell.Row).Copy Destination:=ThisWorkbook.Sheets(3).Range(“A” & currentRow)
    currentRow = currentRow + 1
    NextIteration:
    Next cell

    End Sub

  3. Matt, this looks good. Are you actually doing anything with the `sheet1` array? You Dim and then ReDim it, but as far as I can tell, you’re not actually putting it to use. You can just get rid of that.

    Also, I don’t always do this because I like to be really explicit, but if you’re looking to compact code, then instead of listing out each Long like I do, you can declare them all at once: Dim rowCount1, rowCount2, i, j As Long. Same with any variables of the same type: Dim rng1,rng2 As Range.

    By now you may have already tried “If i.value=j.Offset(0,1).value” and realized that it won’t work. i and j are just numbers. To simplify the syntax, you’d have to make i and j Cell types and have them refer to the cells themselves. There are some alternative references that could help you compact the code somewhat. It just depends on how you like to think about it. I like thinking in terms of offsets, but I have a colleague who prefers the index notation (http://msdn.microsoft.com/en-us/library/office/aa221540%28v=office.11%29.aspx). That’s different enough from my setup that it would take some significant rework. If you’re interested I could show you what I think that would look like, but it probably won’t compact your code enough to make a huge difference.

    Where’s your raw data coming from? Is it in a database? And are the notes getting saved back to the database?

  4. Funny you should mention the sheet1 array. I just came back to this part after doing a bit of cleanup elsewhere in the book and thought.. Hey that’s referring to sheet1… I changed them to sheet9 but didn’t notice a big difference.

    As far as compacting code goes. I’m more about readability then compactness. I like to be able to scan the sheet and find the bit of code I’m after. Like you said, personal preference.

    I could use a little help. The code does seem to accurately match up the exported notes to the new data, but I have observed some odd behavior.

    I run this code to do the export. I put a helper column in to do an if check to find all rows with a value in one of the three note columns. Filter out the falses and copy out the notes. Works great, virtually instantaneous.

    Sub PMnotes()
    Dim i As Range
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Worksheets(“OldPMnotes”).Delete
    Application.DisplayAlerts = True
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = “OldPMnotes”
    Set ws2 = Sheets(“PM”)
    With ws2
    .AutoFilterMode = False
    .Range(“AH5:AH4000”).Formula = “=IF($AE5 > 0,TRUE,IF($AF5>0,TRUE,IF($AG5>0,TRUE,FALSE)))”
    ws2.Range(“AH5:AH4000”).Calculate
    End With
    With ws2.Range(“A4:AH4″)
    .Cells(1).AutoFilter Field:=34, Criteria1:=”True”
    iLastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row
    .Range(“A5:AG4000”).SpecialCells(xlCellTypeVisible).Copy
    Worksheets(“OldPMnotes”).Range(“A” & Rows.Count).End(xlUp).Offset(4, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ws2.AutoFilterMode = False
    ws2.Columns(“AH:AH”).ClearContents
    End With

    End Sub

    The data (pm sheet) is about 3000 rows to column AG. Mix of dates, text and numbers. Once I get all the bugs worked out I will be setting up two more export/import routines to cover two more sheets of about the same size.

    If I do an export then go in and manually clear the “notes” columns and run the import/match the match is instantaneous and flawless. However if I do the export then bring in a new dataset the import/match takes about a minute to complete. Has me thinking maybe the exported data is in an array on the clipboard??

    Knowing that the exported data will probably always match within the first few hundred rows of the PM sheet is there a better way to structure the search so it searches in a direction where it will finish faster?

    Could the delay be caused by no match events?

    I would think that If the last row of exported data is checked against the first row of the new data 80-90% of the data would match right away. Leaving only that 10-20% that would search all 3000 records only to come up with a no match. Not sure if guiding the logic in this way is possible or not but definitely seems like the shortest route from A to B.

    Any help would be appreciated. As this is still a bit buggy for what I need.

  5. Update

    Playing with this code. Looks to bail when the first no match event occurs. Real quick, relatively speaking.

    Help me understand this. The script does cycle through all cell.values before exiting with no match?

    For Each cell In rng1.Cells
    For Each cell2 In rng2.Cells
    i = cell.Row
    j = cell2.Row
    If cell2.Value = cell.Value Then
    If cell2.Offset(0, 1).Value = cell.Offset(0, 1).Value Then
    If cell2.Offset(0, 4).Value = cell.Offset(0, 4).Value Then
    If cell2.Offset(0, 6).Value = cell.Offset(0, 6).Value Then
    If cell2.Offset(0, 7).Value = cell.Offset(0, 7).Value Then
    Worksheets(“PM”).Cells(j, 31) = Worksheets(“OldPMnotes”).Cells(i, 31)
    Worksheets(“PM”).Cells(j, 32) = Worksheets(“OldPMnotes”).Cells(i, 32)
    Worksheets(“PM”).Cells(j, 33) = Worksheets(“OldPMnotes”).Cells(i, 33)
    GoTo NextIteration
    End If
    End If
    End If
    End If
    End If
    Next cell2
    currentRow = currentRow + 1
    NextIteration:
    Next cell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s