magelan
09-09-2013, 01:42 PM
Okay, logic puzzle, trying to figure out how VBA would handle it.
I've got data formatted like...
10 -> 101,102,103
21 -> 211, 212, 213
25 -> 251,252,253, 254, 255
This data represents the "Master List" and it is stored in a 2 dimensional array, like..
Numbers(1 to 30, 1 to 10) with the second dimension being dynamically expanded depending on how many items are in the list for each first dimension [in the case above, 1 to 30, 1 to 5)
The problem is that for this "Master List" I also have lists that have been deployed to the network "Deployed Lists". These "Deployed Lists" have the ability to be different from the "Master List".
So far, my program builds the 2 dimensional array, find all of the deployed lists, and can compare the NumArr() to the actual data using the following code.
For i = 1 To UBound(templateArr, 1)
For k = 1 To UBound(templateArr, 2)
If Not (templateArr(i, k) = "") Then
If Not (workCell.Offset(0, -1).value = "") Then
'The above line stops it from looking at blank entries
currentFirstDimension = workCell.Offset(0, -1).value
'This sets CurrentFirstDimension to what is in the DeployedList as storage
End If
currentSecondDimension = workCell.value
'sets currentSecondDimension to what is in the deployedList as storage
If Not (workCell.value = templateArr(i, k)) Then
'compare currentSecondDimension to what exists in the template
'inadvertantly makes sure the firstdimension is correct as well
Debug.print "MISMATCH FOUND AT " & workCell.address & " with Values " & workCell.value & " not matching " & templateArr(i,k)
End If
If Not (currentFirstDimension = i) Then
'if there were more rows in the deployedList than were items in the templateArr, extra rows were found [deletion has to occur]
debug.print "EXTRA ROWS FOUND: TOO MANY VALUES IN FIRSTDIMENSION OF " & currentFirstDimension & " VERSUS FIRSTDIMENSION OF " & i
End If
Set workCell = workCell.Offset(1, 0)
End If
Next
Next
as you can see, the loop just iterates through my templateArr, comparing the first dimension + second dimension to each corresponding workCell.
MY PROBLEM IS:
How do I enact any changes? I need to be able to remove rows that do not match [eg if 25 -> 251, 252, 253 became 25 -> 251, 253] how do I catch that? Obviously, i would see that 25 -> 252 exists, and delete that row, then check the next row and find 25-> 253, and be OKAY.
BUT how then do i determine if an addition needs to be made?
If 25 -> 251, 252, 253 became 25 -> 251, 252, 2522, 253, i would see 25 -> 252, be okay, then be expecting 2522 and see 253, delete 253, then proceed to delete rows...all the way until the end of the book, having never found 2522.
Does anyone have a method from which I can take my 2 dimensional loop, and basically diff + patch it into the data i'm looking at?
EDIT: as an example, here is what my data looks like, and a sample of what my templateArr looks like. There are blank entries whenever the first dimension doesnt exist, as well as when the second dimension doesnt exist. Workcell.offset(0,-1) looks at the firstdimension [would find 10,15,25,etc] in the code.1056910569
I've got data formatted like...
10 -> 101,102,103
21 -> 211, 212, 213
25 -> 251,252,253, 254, 255
This data represents the "Master List" and it is stored in a 2 dimensional array, like..
Numbers(1 to 30, 1 to 10) with the second dimension being dynamically expanded depending on how many items are in the list for each first dimension [in the case above, 1 to 30, 1 to 5)
The problem is that for this "Master List" I also have lists that have been deployed to the network "Deployed Lists". These "Deployed Lists" have the ability to be different from the "Master List".
So far, my program builds the 2 dimensional array, find all of the deployed lists, and can compare the NumArr() to the actual data using the following code.
For i = 1 To UBound(templateArr, 1)
For k = 1 To UBound(templateArr, 2)
If Not (templateArr(i, k) = "") Then
If Not (workCell.Offset(0, -1).value = "") Then
'The above line stops it from looking at blank entries
currentFirstDimension = workCell.Offset(0, -1).value
'This sets CurrentFirstDimension to what is in the DeployedList as storage
End If
currentSecondDimension = workCell.value
'sets currentSecondDimension to what is in the deployedList as storage
If Not (workCell.value = templateArr(i, k)) Then
'compare currentSecondDimension to what exists in the template
'inadvertantly makes sure the firstdimension is correct as well
Debug.print "MISMATCH FOUND AT " & workCell.address & " with Values " & workCell.value & " not matching " & templateArr(i,k)
End If
If Not (currentFirstDimension = i) Then
'if there were more rows in the deployedList than were items in the templateArr, extra rows were found [deletion has to occur]
debug.print "EXTRA ROWS FOUND: TOO MANY VALUES IN FIRSTDIMENSION OF " & currentFirstDimension & " VERSUS FIRSTDIMENSION OF " & i
End If
Set workCell = workCell.Offset(1, 0)
End If
Next
Next
as you can see, the loop just iterates through my templateArr, comparing the first dimension + second dimension to each corresponding workCell.
MY PROBLEM IS:
How do I enact any changes? I need to be able to remove rows that do not match [eg if 25 -> 251, 252, 253 became 25 -> 251, 253] how do I catch that? Obviously, i would see that 25 -> 252 exists, and delete that row, then check the next row and find 25-> 253, and be OKAY.
BUT how then do i determine if an addition needs to be made?
If 25 -> 251, 252, 253 became 25 -> 251, 252, 2522, 253, i would see 25 -> 252, be okay, then be expecting 2522 and see 253, delete 253, then proceed to delete rows...all the way until the end of the book, having never found 2522.
Does anyone have a method from which I can take my 2 dimensional loop, and basically diff + patch it into the data i'm looking at?
EDIT: as an example, here is what my data looks like, and a sample of what my templateArr looks like. There are blank entries whenever the first dimension doesnt exist, as well as when the second dimension doesnt exist. Workcell.offset(0,-1) looks at the firstdimension [would find 10,15,25,etc] in the code.1056910569