How to compare two columns for a specified string?
 

Could someone help me on the following?

Column A contains a list of string values and so does column C. The
strings in both columns have corresponding numerical values in columns
B and D. I need a routine (macro?) that cheks if a certain string
exists in both columns A and C.If the same string is found in both,
nothing else is done and if a certain
string is only found in column C, that string and the corresponging
value in column D are deleted. The procedure should be repeated until
the lists contain the only the same strings. In other words the goal is
that both lists would become identical, ie the strings in column C
missing from column A would be deleted.


Thanks,
jarmo

How to compare two columns for a specified string?
 

Use a loop through column C
(code not tested)

====================================
Dim x as Long, lrw as Long

lrw = Cells(Rows.COUNT, "C").End(xlUp).Row

For x = 1 to lrw
If worksheetfunction.countif(Columns(1), Cells(x,3)) = 0 then
Range(cells(x,3),cells(x,4)).ClearContents
End If
Next
===========================================
or delete the cells
Range(cells(x,3),cells(x,4)).Delete Shift:=xlUp

Be sure to work from the bottom up...
For x = lrw to 1 step -1
--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
oups.com...
Could someone help me on the following?

Column A contains a list of string values and so does column C. The
strings in both columns have corresponding numerical values in columns
B and D. I need a routine (macro?) that cheks if a certain string
exists in both columns A and C.If the same string is found in both,
nothing else is done and if a certain
string is only found in column C, that string and the corresponging
value in column D are deleted. The procedure should be repeated until
the lists contain the only the same strings. In other words the goal is
that both lists would become identical, ie the strings in column C
missing from column A would be deleted.


Thanks,
jarmo

How to compare two columns for a specified string?
 

Sorry, forgot to mention the versions: Win XP and Excel 97..

Steve, I didn't get your macro to work, it gave the following message;
"Run-time error 1004, application-defined or object-defined error".

I don't think I can find out what the problem is, being a real beginner in excel..

Any suggestions ..?

cheers,
jarski

"STEVE BELL" wrote in message news:<aVfye.168$kh3.23@trnddc03...
Use a loop through column C
(code not tested)

====================================
Dim x as Long, lrw as Long

lrw = Cells(Rows.COUNT, "C").End(xlUp).Row

For x = 1 to lrw
If worksheetfunction.countif(Columns(1), Cells(x,3)) = 0 then
Range(cells(x,3),cells(x,4)).ClearContents
End If
Next
===========================================
or delete the cells
Range(cells(x,3),cells(x,4)).Delete Shift:=xlUp

Be sure to work from the bottom up...
For x = lrw to 1 step -1
--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
oups.com...
Could someone help me on the following?

Column A contains a list of string values and so does column C. The
strings in both columns have corresponding numerical values in columns
B and D. I need a routine (macro?) that cheks if a certain string
exists in both columns A and C.If the same string is found in both,
nothing else is done and if a certain
string is only found in column C, that string and the corresponging
value in column D are deleted. The procedure should be repeated until
the lists contain the only the same strings. In other words the goal is
that both lists would become identical, ie the strings in column C
missing from column A would be deleted.


Thanks,
jarmo

How to compare two columns for a specified string?
 

Apparently I messed up... Excel wants the sheet to be the activesheet...
So I added the sheet select and spelled out which sheet to work on.

================================================== ==============
Dim x As Long, lrw As Long

Sheets("Sheet1").Select

lrw = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

For x = 1 To lrw
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns (1),
Sheets("Sheet1").Cells(x, 3)) = 0 Then
Sheets("Sheet1").Range(Cells(x, 3), Cells(x, 4)).ClearContents
End If
Next
================================================== ===

And don't fret about being a beginner. Been there, done that. And this ng
is a great place for you to
cut your teeth...

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
om...
Sorry, forgot to mention the versions: Win XP and Excel 97..

Steve, I didn't get your macro to work, it gave the following message;
"Run-time error 1004, application-defined or object-defined error".

I don't think I can find out what the problem is, being a real beginner in
excel..

Any suggestions ..?

cheers,
jarski

"STEVE BELL" wrote in message
news:<aVfye.168$kh3.23@trnddc03...
Use a loop through column C
(code not tested)

====================================
Dim x as Long, lrw as Long

lrw = Cells(Rows.COUNT, "C").End(xlUp).Row

For x = 1 to lrw
If worksheetfunction.countif(Columns(1), Cells(x,3)) = 0 then
Range(cells(x,3),cells(x,4)).ClearContents
End If
Next
===========================================
or delete the cells
Range(cells(x,3),cells(x,4)).Delete Shift:=xlUp

Be sure to work from the bottom up...
For x = lrw to 1 step -1
--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
oups.com...
Could someone help me on the following?

Column A contains a list of string values and so does column C. The
strings in both columns have corresponding numerical values in columns
B and D. I need a routine (macro?) that cheks if a certain string
exists in both columns A and C.If the same string is found in both,
nothing else is done and if a certain
string is only found in column C, that string and the corresponging
value in column D are deleted. The procedure should be repeated until
the lists contain the only the same strings. In other words the goal is
that both lists would become identical, ie the strings in column C
missing from column A would be deleted.


Thanks,
jarmo

How to compare two columns for a specified string?
 

Yes! Works fine.
Thank you so much.

jarski



"STEVE BELL" wrote in message news:<Ajxye.28580$Fn4.27714@trnddc06...
Apparently I messed up... Excel wants the sheet to be the activesheet...
So I added the sheet select and spelled out which sheet to work on.

================================================== ==============
Dim x As Long, lrw As Long

Sheets("Sheet1").Select

lrw = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

For x = 1 To lrw
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns (1),
Sheets("Sheet1").Cells(x, 3)) = 0 Then
Sheets("Sheet1").Range(Cells(x, 3), Cells(x, 4)).ClearContents
End If
Next
================================================== ===

And don't fret about being a beginner. Been there, done that. And this ng
is a great place for you to
cut your teeth...

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
om...
Sorry, forgot to mention the versions: Win XP and Excel 97..

Steve, I didn't get your macro to work, it gave the following message;
"Run-time error 1004, application-defined or object-defined error".

I don't think I can find out what the problem is, being a real beginner in
excel..

Any suggestions ..?

cheers,
jarski

"STEVE BELL" wrote in message
news:<aVfye.168$kh3.23@trnddc03...
Use a loop through column C
(code not tested)

====================================
Dim x as Long, lrw as Long

lrw = Cells(Rows.COUNT, "C").End(xlUp).Row

For x = 1 to lrw
If worksheetfunction.countif(Columns(1), Cells(x,3)) = 0 then
Range(cells(x,3),cells(x,4)).ClearContents
End If
Next
===========================================
or delete the cells
Range(cells(x,3),cells(x,4)).Delete Shift:=xlUp

Be sure to work from the bottom up...
For x = lrw to 1 step -1
--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
oups.com...
Could someone help me on the following?

Column A contains a list of string values and so does column C. The
strings in both columns have corresponding numerical values in columns
B and D. I need a routine (macro?) that cheks if a certain string
exists in both columns A and C.If the same string is found in both,
nothing else is done and if a certain
string is only found in column C, that string and the corresponging
value in column D are deleted. The procedure should be repeated until
the lists contain the only the same strings. In other words the goal is
that both lists would become identical, ie the strings in column C
missing from column A would be deleted.


Thanks,
jarmo

How to compare two columns for a specified string?
 

Great!

Glad to be of help...

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
om...
Yes! Works fine.
Thank you so much.

jarski



"STEVE BELL" wrote in message
news:<Ajxye.28580$Fn4.27714@trnddc06...
Apparently I messed up... Excel wants the sheet to be the activesheet...
So I added the sheet select and spelled out which sheet to work on.

================================================== ==============
Dim x As Long, lrw As Long

Sheets("Sheet1").Select

lrw = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

For x = 1 To lrw
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns (1),
Sheets("Sheet1").Cells(x, 3)) = 0 Then
Sheets("Sheet1").Range(Cells(x, 3), Cells(x, 4)).ClearContents
End If
Next
================================================== ===

And don't fret about being a beginner. Been there, done that. And this
ng
is a great place for you to
cut your teeth...

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
om...
Sorry, forgot to mention the versions: Win XP and Excel 97..

Steve, I didn't get your macro to work, it gave the following message;
"Run-time error 1004, application-defined or object-defined error".

I don't think I can find out what the problem is, being a real beginner
in
excel..

Any suggestions ..?

cheers,
jarski

"STEVE BELL" wrote in message
news:<aVfye.168$kh3.23@trnddc03...
Use a loop through column C
(code not tested)

====================================
Dim x as Long, lrw as Long

lrw = Cells(Rows.COUNT, "C").End(xlUp).Row

For x = 1 to lrw
If worksheetfunction.countif(Columns(1), Cells(x,3)) = 0 then
Range(cells(x,3),cells(x,4)).ClearContents
End If
Next
===========================================
or delete the cells
Range(cells(x,3),cells(x,4)).Delete Shift:=xlUp

Be sure to work from the bottom up...
For x = lrw to 1 step -1
--
steveB

Remove "AYN" from email to respond
"jarski" wrote in message
oups.com...
Could someone help me on the following?

Column A contains a list of string values and so does column C. The
strings in both columns have corresponding numerical values in
columns
B and D. I need a routine (macro?) that cheks if a certain string
exists in both columns A and C.If the same string is found in both,
nothing else is done and if a certain
string is only found in column C, that string and the corresponging
value in column D are deleted. The procedure should be repeated
until
the lists contain the only the same strings. In other words the goal
is
that both lists would become identical, ie the strings in column C
missing from column A would be deleted.


Thanks,
jarmo