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

Remove "AYN" from email to respond
"jarski" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >

[jarski]

Yes! Works fine.
Thank you so much.

jarski



"STEVE BELL" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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]

Great!

Glad to be of help...

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"jarski" <[email protected]> wrote in message
news:[email protected]...
> Yes! Works fine.
> Thank you so much.
>
> jarski
>
>
>
> "STEVE BELL" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >