| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| compare string | Excel Worksheet Functions | |||
| Compare two columns | Excel Discussion (Misc queries) | |||
| Compare between String and Value using VBA | Excel Programming | |||
| string compare with wildcards | Excel Programming | |||
| Compare columns of value | Excel Programming | |||
Hybrid Mode
