PDA

View Full Version : Logic



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

snb
09-10-2013, 12:12 AM
To me it's as clear as mud.
Why not illustrating the desired result in the posted workbook ?

magelan
09-11-2013, 08:50 AM
Here is a full workbook with exactly what I'm looking for. Basically i need to take Template Data, look at Deployed Data, and make it into Template Data.

What is not pictured is that i cant simply "copy paste" the data ontop because there is data existing to the right of the rows that needs to stick with its entry.. eg, 25 -> 251 might have 10 cells to the right of it that need to be removed, or moved up/down, depending on if there is an addition made. If it was as easy as just pasting this array on top of the old data and cleaning up anything extra on the bottom, that would be too easy.

magelan
09-16-2013, 06:26 AM
Guess all the helpful guys left when this site updated..

Paul_Hossler
09-16-2013, 08:00 AM
No, I believe that the helpful guys are still here.

Paul

mikerickson
09-16-2013, 11:08 AM
From the worksheet, it looks like
TempleArray(i,j) = Range("B3").Offset(Application.Match(i, Range("B3:B100"),0) + j - 1, 2)

magelan
09-16-2013, 11:53 AM
From the worksheet, it looks like
TempleArray(i,j) = Range("B3").Offset(Application.Match(i, Range("B3:B100"),0) + j - 1, 2)

What is that supposed to do? I already have a loop in place that will match each individual entry in the templateArr to the corresponding cell on the worksheet, my problem is, when my Template differs from the Data, what would the logic be to determine if it is a deletion of the row in the Data, or an addition of the row in the Template?

basically if this was linux, how do i diff & patch this template?

SamT
09-16-2013, 06:44 PM
The code in your last example is incomplete and will not work

This code satisfies your example, but only because your "full workbook with exactly what I'm looking for" isn't full. Nor is it exactly what you are looking for because it doesn't have any columns to the right that must be handled.

Sub Replace()
Range(Range("B2), Range("C3").End(xlDown)).Copy Range("F20")
End Sub

Just like the first example you posted, it ain't all there. Do you really think we can see your computer from our houses?

The code in your second example is not only incomplete, but it is using a fixed size array and you have previously stated that you need a variable sized array, and I did not see any "Redim Preserve" statements in your (very incomplete) code.

When you PO'ed snb with your "Read my mind and do what I want, not what I say" attitude, you lost a Master coder who could have solved your code problem with three lines (at most) of code.

We are all volunteers here and you can't force any of us to help you.

If you do want some help, you must be forthcoming with enough, even too much, accurate information about the situation. You must also show some courtesy and consideration for us.

Take for example, the first 6 words in your reply to Mike's offer of help. You basically told him that your cup of knowledge was too full to hold any of his.