Hey guys

I have 2 Columns of data. Columns M and N. The data in
both columns starts in Row2 and goes through Row100. I
need a code that will have ColumnN look in ColumnM for
values. If a value is in ColumnM, that same value needs
to be in ColumnN. The code needs to put that value at the
end of the last value in ColumN. Also if a value that was
in ColumnM is deleted, then that value will need to be
removed from columnN.

This means the values in columnM can change in 3 ways.
The order in which the values are listed in columnM may
change, a new value will be added to columnM, or a value
will be deleted in columnM. All I need for ColumnN to do
is just make sure all the values in ColumnM are in ColumnN
and no more. This means the values in columnN can change
in only 2 ways. Either by deleting a Value(only if the
value is no longer in ColumnM) or by adding a value(only
if a new value is added to ColumnM).

Thanx

Todd Huttenstine

Both columns have the same number of rows. Column N is supposed to contain the same values that
are in column M. So why not just put a formula in N2, =M2, and copying down through N100 ??


On Sun, 30 Nov 2003 17:12:35 -0800, "Todd Huttenstine"
wrote:

Hey guys

I have 2 Columns of data. Columns M and N. The data in
both columns starts in Row2 and goes through Row100. I
need a code that will have ColumnN look in ColumnM for
values. If a value is in ColumnM, that same value needs
to be in ColumnN. The code needs to put that value at the
end of the last value in ColumN. Also if a value that was
in ColumnM is deleted, then that value will need to be
removed from columnN.

This means the values in columnM can change in 3 ways.
The order in which the values are listed in columnM may
change, a new value will be added to columnM, or a value
will be deleted in columnM. All I need for ColumnN to do
is just make sure all the values in ColumnM are in ColumnN
and no more. This means the values in columnN can change
in only 2 ways. Either by deleting a Value(only if the
value is no longer in ColumnM) or by adding a value(only
if a new value is added to ColumnM).

Thanx

Todd Huttenstine

That will not work because the values cannot change in
ColumnN if the only thing thats happening is the values
are just moving around in ColumnM.

I am trying to create more of a constant with ColumnN.




-----Original Message-----
Both columns have the same number of rows. Column N is
supposed to contain the same values that
are in column M. So why not just put a formula in N2,
=M2, and copying down through N100 ??


On Sun, 30 Nov 2003 17:12:35 -0800, "Todd Huttenstine"

wrote:

Hey guys

I have 2 Columns of data. Columns M and N. The data in
both columns starts in Row2 and goes through Row100. I
need a code that will have ColumnN look in ColumnM for
values. If a value is in ColumnM, that same value needs
to be in ColumnN. The code needs to put that value at
the
end of the last value in ColumN. Also if a value that
was
in ColumnM is deleted, then that value will need to be
removed from columnN.

This means the values in columnM can change in 3 ways.
The order in which the values are listed in columnM may
change, a new value will be added to columnM, or a value
will be deleted in columnM. All I need for ColumnN to
do
is just make sure all the values in ColumnM are in
ColumnN
and no more. This means the values in columnN can
change
in only 2 ways. Either by deleting a Value(only if the
value is no longer in ColumnM) or by adding a value(only
if a new value is added to ColumnM).

Thanx

Todd Huttenstine

.

On Sun, 30 Nov 2003 17:54:34 -0800, Todd Huttenstine wrote:

That will not work because the values cannot change in
ColumnN if the only thing thats happening is the values
are just moving around in ColumnM.

How do you figure? The only reason to do it any other way is if you want
to keep track of the order that things are happening. Otherwise, Myrna's
suggestion is the obvious solution.

Well... you can try this - it's a quick hack but it works OK on my end.

Sub checkChanges()
n = 101
For x1 = 2 To 100 'check n for values of m
For x2 = 2 To 100
If Cells(x2, 14).Value = Cells(x1, 13).Value Then GoTo Cont1
Next
Cells(x1, 13).Copy Cells(n, 14)
n = n + 1
Cont1:
DoEvents
Next
For x1 = 2 To n - 1 'check m for values of n
For x2 = 2 To 100
If Cells(x1, 14).Value = Cells(x2, 13).Value Then GoTo Cont2
Next
Cells(x1, 14).Delete xlShiftUp
x1 = x1 - 1
n = n - 1
If n < 101 Then Exit For
Cont2:
DoEvents
Next
End Sub

--
auric "underscore" "underscore" "at" hotmail "dot" com
*****
I'm just a *******, but at least I admit it.
-- Slipknot (My Plague, Resident Evil OST)

Ok I was not clear sorry. Let me try again...

I have columns M and N. The initial action will be in
ColumnM. ColumnN looks at the values in ColumnM.

In ColumnM, Values can be added, deleted, or the order in
which the values are listed can be switched around. If
the values in ColumnM are switched around, I dont want
ColumnN to swicth the values to Mirror ColumnM. The only
way the values in ColumnN can be modified is either if a
new value is added to columnM or a value is deleted from
columnM.

Thanx

Todd

"I
need a code that will have ColumnN look in ColumnM for
values."

Where do these values reside? ColumnN? If the values
aren't found in M, does it get permanently deleted from
N? Such that, if the value gets reinstated at M, N will
not anymore reflect the value?

Do you mind supplying exact data?


-----Original Message-----
Ok I was not clear sorry. Let me try again...

I have columns M and N. The initial action will be in
ColumnM. ColumnN looks at the values in ColumnM.

In ColumnM, Values can be added, deleted, or the order
in
which the values are listed can be switched around. If
the values in ColumnM are switched around, I dont want
ColumnN to swicth the values to Mirror ColumnM. The
only
way the values in ColumnN can be modified is either if a
new value is added to columnM or a value is deleted from
columnM.

Thanx

Todd
.
\

On Sun, 30 Nov 2003 18:34:14 -0800, Todd Huttenstine wrote:

Ok I was not clear sorry. Let me try again...

I have columns M and N. The initial action will be in
ColumnM. ColumnN looks at the values in ColumnM.

In ColumnM, Values can be added, deleted, or the order in
which the values are listed can be switched around. If
the values in ColumnM are switched around, I dont want
ColumnN to swicth the values to Mirror ColumnM. The only
way the values in ColumnN can be modified is either if a
new value is added to columnM or a value is deleted from
columnM.

In the VBA editor, open up the Sheet (not a module). Paste in the code I
posted and change the line with sub's name to this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

My code can only handle rows 2-100 for column M, so if your data might
go beyond that, modify appropriately.
--
auric "underscore" "underscore" "at" hotmail "dot" com
*****
43% of all statistics are worthless.

I guess the code would be something like a Match triggered code, where Range
N2:N100 needs to be updated to match values in Range M2:M100, however it
needs to ignore existing values being rearranged in ColumnM. Below is a
detailed explanation...

The value will be input, changed, or deleted in columnM within Range
M2:M100. The number of cells that contain data in this range will change
from time to time and there will never be any cells within the range that
are empty. For instance, if cells M2:M21 contains values(data), then there
will not be any cells within this range (Range M2:M21) that are blank. The
actual range containing values will vary from time to time but the values
will always be within Range M2:M100. Lets say the range that currently
contains values within Range M2:M100 is Range M2:M5. Cell M2="Dog", cell
M3="Cat", cell M4="Rat", and cell M5="Pig". I need for Range N2="Dog", cell
N3="Cat", cell N4="Rat", and cell N5="Pig". Now if the data were to ONLY
get rearranged in ColumnM to where lets say M2="Cat", cell M3="Dog", cell
M4="Rat", and cell M5="Pig", then the values in ColumnN would NOT change
because the values in ColumnN still match the values in ColumnM (meaning no
new data was entered or no data was deleted, it was just rearranged).


If a value is deleted or added in the range in ColumnM, I need for the code
to run to re-sync so all the values in ColumnN match the values in ColumnM.
Lets say the actual range containing values in ColumnM is Range M2:M5 and
the value "Cat" was deleted. Now when the match code is run and the code
sees Cat was deleted from ColumnM(cell M3) and still sees it in ColumnN, it
would not match and therfore trigger the code to run to re-sync the values
so all the data matches data in ColumnM.

Now as for new values being added to ColumnM: The same process occurs.
When the match code runs, it will see there is not a match in data between
the 2 columns. With this being said, the code would need to be re-run. If
the code is re-run, the data will re-sync together so that all values are
matching.






"Yanar99" wrote in message
...
"I
need a code that will have ColumnN look in ColumnM for
values."

Where do these values reside? ColumnN? If the values
aren't found in M, does it get permanently deleted from
N? Such that, if the value gets reinstated at M, N will
not anymore reflect the value?

Do you mind supplying exact data?


-----Original Message-----
Ok I was not clear sorry. Let me try again...

I have columns M and N. The initial action will be in
ColumnM. ColumnN looks at the values in ColumnM.

In ColumnM, Values can be added, deleted, or the order
in
which the values are listed can be switched around. If
the values in ColumnM are switched around, I dont want
ColumnN to swicth the values to Mirror ColumnM. The
only
way the values in ColumnN can be modified is either if a
new value is added to columnM or a value is deleted from
columnM.

Thanx

Todd
.
\