I have a problem I am tryign to solve using VBA. I was wondering of anyone
was up to the challenge in helping me solve the issue. I have two sets of
data stored in two dictionaries. I want to be able to compare the data and
solve and come up with a solution to what has changed in the revised data.

Assume I have que. In this que, I have empty spots. For fun lets say its a
que at a movie theatre with people in line who can also save spots for
friends who have not arrived yet. The empty spots are as follows:

Position 157
Position 153
Position 152
Position 136
Position 106
Position 105
Position 104
Position 103
Position 95
Position 59
Position 35
Position 3

Then a bunch of new empty positions are created for the que. For fun lets
say that some new people enter the que and/or these new or existing people
save additional spots for friends. The new empty slots in the que are as
follows:

Position 163
Position 162
Position 161
Position 160
Position 158
Position 154
Position 153
Position 137
Position 107
Position 106
Position 105
Position 104
Position 96
Position 60
Position 36
Position 20
Position 3

I will always know in advance what the total difference in positions (empty
+ filled) is. In this example I will know in advance that the ques differ by
6 spaces. And I will always have the two resulting data sets. I need a
function to figure out where the empty slots were created in the original que
and where the new filled spots are. I want to be able to compare the data in
the ques (dictionary object) and attempt to figure out where the 6 spaces
came about.

You know from looking at the data above that Position 3 is still the same.
So any new position had to be created after Position 3.

The correct answer here is that I created 1 new empty spot at Position 20, 4
new empty spots at Positions 159-162, and 1 new filled spot at Position 163.
1 + 4 + 1 = 6.

Can anyone provide me with some insight as to how to compare these data sets
to solve this problem? I might be more of a math question than a VBA
question but I since I am using VBA, it will still be helpful!

Thanks

EM

I put 1 column of numbers (deleted word postiion) in column A and the other
in column B and ran this macro. This is similar to code I wrote for somebody
last week. It is esier doing this in excel the using arrays because in excel
you can easily add rows.

When using arrays if you put a piece of data in the middle you have to push
all the members of the array down one position.

I like doing things like this in C Language where you can easily create
linked lists using pointers. In C to add a member in the middle of an array
you allocate memory for the new item and the you use pointer to add the new
member into a linked list.

Sub lineupdata()

RowCount = 1
Do While Range("A" & RowCount) < "" Or _
Range("B" & RowCount) < ""

Select Case (Range("B" & RowCount).Value - Range("A" & RowCount).Value)
Case Is < 0
Range("B" & RowCount).Insert shift:=xlShiftDown
Case Is = 0
'Do nothing
Case Is 0
Range("A" & RowCount).Insert shift:=xlShiftDown
End Select

RowCount = RowCount + 1
Loop

End Sub


"ExcelMonkey" wrote:

I have a problem I am tryign to solve using VBA. I was wondering of anyone
was up to the challenge in helping me solve the issue. I have two sets of
data stored in two dictionaries. I want to be able to compare the data and
solve and come up with a solution to what has changed in the revised data.

Assume I have que. In this que, I have empty spots. For fun lets say its a
que at a movie theatre with people in line who can also save spots for
friends who have not arrived yet. The empty spots are as follows:

Position 157
Position 153
Position 152
Position 136
Position 106
Position 105
Position 104
Position 103
Position 95
Position 59
Position 35
Position 3

Then a bunch of new empty positions are created for the que. For fun lets
say that some new people enter the que and/or these new or existing people
save additional spots for friends. The new empty slots in the que are as
follows:

Position 163
Position 162
Position 161
Position 160
Position 158
Position 154
Position 153
Position 137
Position 107
Position 106
Position 105
Position 104
Position 96
Position 60
Position 36
Position 20
Position 3

I will always know in advance what the total difference in positions (empty
+ filled) is. In this example I will know in advance that the ques differ by
6 spaces. And I will always have the two resulting data sets. I need a
function to figure out where the empty slots were created in the original que
and where the new filled spots are. I want to be able to compare the data in
the ques (dictionary object) and attempt to figure out where the 6 spaces
came about.

You know from looking at the data above that Position 3 is still the same.
So any new position had to be created after Position 3.

The correct answer here is that I created 1 new empty spot at Position 20, 4
new empty spots at Positions 159-162, and 1 new filled spot at Position 163.
1 + 4 + 1 = 6.

Can anyone provide me with some insight as to how to compare these data sets
to solve this problem? I might be more of a math question than a VBA
question but I since I am using VBA, it will still be helpful!

Thanks

EM

Thanks for the interest! I see what you have done. You have created a
visual which illustrates the two data sets side-by-side as seen below in
Column A and Column B. You can not eye-ball where the insertions likely
occured. I need to now summarize the results as follows:

Empty spots that remained the same: 3

Empty spots that Moved:
35(1),59(1),95(1),103(1),104(1),105(1),106(1),136( 1),152(1),153(1),157(1)

New empty spots: 20,160,161,162,163

Note the () above denote the movement of the original spot (i.e. (1) means
it moved 1 spot.

I assuming that I have to loop through column B and compare to the values in
column A using the maximum difference (6 in this example) that I will always
know in advance as some sort of range marker. So because 3 exists in both
columns, it has remained the same. Because 20 does not exist and is not
within 6 spaces of the next position, it likely is a new spot. Because 36 is
within 6 spots of the previous value in the first column (35) and not withing
6 spots of the next highest value in first column (59) it must be a shifted
position stemming from 35 (i.e. 1 shift). I am assuming that you would have
to delete the values in column A when successufully compared so that when you
get to 160 in column B you would test to see if there are any previous values
in A to compare it to. If not, then it would qualify as a new empty slot.
(Just thinking out loud here). Once I have this licked in Excel I will try
to migrate it into code.

Col A Col B
163
162
161
160
158
157
154
153 153
152
137
136
107
106 106
105 105
104 104
103
96
95
60
59
36
35
20
3 3

Thanks

EM

"Joel" wrote:

I put 1 column of numbers (deleted word postiion) in column A and the other
in column B and ran this macro. This is similar to code I wrote for somebody
last week. It is esier doing this in excel the using arrays because in excel
you can easily add rows.

When using arrays if you put a piece of data in the middle you have to push
all the members of the array down one position.

I like doing things like this in C Language where you can easily create
linked lists using pointers. In C to add a member in the middle of an array
you allocate memory for the new item and the you use pointer to add the new
member into a linked list.

Sub lineupdata()

RowCount = 1
Do While Range("A" & RowCount) < "" Or _
Range("B" & RowCount) < ""

Select Case (Range("B" & RowCount).Value - Range("A" & RowCount).Value)
Case Is < 0
Range("B" & RowCount).Insert shift:=xlShiftDown
Case Is = 0
'Do nothing
Case Is 0
Range("A" & RowCount).Insert shift:=xlShiftDown
End Select

RowCount = RowCount + 1
Loop

End Sub


"ExcelMonkey" wrote:

I have a problem I am tryign to solve using VBA. I was wondering of anyone
was up to the challenge in helping me solve the issue. I have two sets of
data stored in two dictionaries. I want to be able to compare the data and
solve and come up with a solution to what has changed in the revised data.

Assume I have que. In this que, I have empty spots. For fun lets say its a
que at a movie theatre with people in line who can also save spots for
friends who have not arrived yet. The empty spots are as follows:

Position 157
Position 153
Position 152
Position 136
Position 106
Position 105
Position 104
Position 103
Position 95
Position 59
Position 35
Position 3

Then a bunch of new empty positions are created for the que. For fun lets
say that some new people enter the que and/or these new or existing people
save additional spots for friends. The new empty slots in the que are as
follows:

Position 163
Position 162
Position 161
Position 160
Position 158
Position 154
Position 153
Position 137
Position 107
Position 106
Position 105
Position 104
Position 96
Position 60
Position 36
Position 20
Position 3

I will always know in advance what the total difference in positions (empty
+ filled) is. In this example I will know in advance that the ques differ by
6 spaces. And I will always have the two resulting data sets. I need a
function to figure out where the empty slots were created in the original que
and where the new filled spots are. I want to be able to compare the data in
the ques (dictionary object) and attempt to figure out where the 6 spaces
came about.

You know from looking at the data above that Position 3 is still the same.
So any new position had to be created after Position 3.

The correct answer here is that I created 1 new empty spot at Position 20, 4
new empty spots at Positions 159-162, and 1 new filled spot at Position 163.
1 + 4 + 1 = 6.

Can anyone provide me with some insight as to how to compare these data sets
to solve this problem? I might be more of a math question than a VBA
question but I since I am using VBA, it will still be helpful!

Thanks

EM