How to compare two columns for a specified string?

[jarski]

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

 

[STEVE BELL]

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

 

[jarski]

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]

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...

 

[jarski]

Yes! Works fine.
Thank you so much.

jarski

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...

 

[STEVE BELL]

Great!

Glad to be of help...

keep on Exceling...