Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for "Select where in" type function
Hi Group,
I'm trying to work with some log files and have been directed to do this in Excel. I'm mostly a DB guy and usually only use Excel 2007 for preping data for a DB load, I need your help please. The log files are just lines in a text file. In excel I have one column with a row for each log entry. I need to perform two processing steps on this data, both using something like a "Select where in" SQL function. I have two lists. The first list has "good" items like "Record Inserted". The Second list is a list of "bad" key words; like "Error" or "Warning. What I am trying to do step throught the column, processing each row. (I know how to navigate, but the processing is the issue) The rules for each row/cell a 1) If the cell value exactly matches any "Good" value, the row is deleted. 2) If the cell contains any of the words in the "Bad" list, the cell content is moved (to another sheet?) and the row is deleted from the log. These rules are applied is this order. Remaining rows are untouched. Any help would be appreciated, Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for "Select where in" type function
A small modification to your code. Since each line will eventually be
deleted, I would just copy the good items in the list as you come them, ignore the bad items and delete the entire column when through. I reversed the order of processing (this keeps the order of copied items in the same order they appear) and removed the repetitive search for the end of the list on sheet3 (putting the incrementer in the good item's Case block... Sub trythis() mc = 1 'Column A dlr = Sheets("sheet3").Cells(Rows.Count, "a").End(xlUp).Row + 1 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Cells(i, mc) Case Is = "c", "d", "e" 'Good items Cells(i, mc).Copy Sheet("sheet3").Cells(dlr, "a") dlr = dlr + 1 End Select Next i Columns(mc).Clear End Sub -- Rick (MVP - Excel) "Don Guillett" wrote in message ... Something like this Sub trythis() mc = 1 'column A For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 dlr = Sheets("sheet3").Cells(Rows.Count, "a").End(xlUp).Row + 1 Select Case Cells(i, mc) Case Is = "a", "b" Rows(i).Delete Case Is = "c", "d", "e" Cells(i, mc).Copy Sheet("sheet3").Cells(dlr, "a") Rows(i).Delete Case Else End Select Next i Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "randy.buchholz" wrote in message ... Hi Group, I'm trying to work with some log files and have been directed to do this in Excel. I'm mostly a DB guy and usually only use Excel 2007 for preping data for a DB load, I need your help please. The log files are just lines in a text file. In excel I have one column with a row for each log entry. I need to perform two processing steps on this data, both using something like a "Select where in" SQL function. I have two lists. The first list has "good" items like "Record Inserted". The Second list is a list of "bad" key words; like "Error" or "Warning. What I am trying to do step throught the column, processing each row. (I know how to navigate, but the processing is the issue) The rules for each row/cell a 1) If the cell value exactly matches any "Good" value, the row is deleted. 2) If the cell contains any of the words in the "Bad" list, the cell content is moved (to another sheet?) and the row is deleted from the log. These rules are applied is this order. Remaining rows are untouched. Any help would be appreciated, Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for "Select where in" type function
Of course, after saying I would, I forgot to change the order of processing
in the loop... Sub trythis() mc = 1 'Column A dlr = Sheets("sheet3").Cells(Rows.Count, "a").End(xlUp).Row + 1 For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row Select Case Cells(i, mc) Case Is = "c", "d", "e" 'Good items Cells(i, mc).Copy Sheet("sheet3").Cells(dlr, "a") dlr = dlr + 1 End Select Next i Columns(mc).Clear End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... A small modification to your code. Since each line will eventually be deleted, I would just copy the good items in the list as you come them, ignore the bad items and delete the entire column when through. I reversed the order of processing (this keeps the order of copied items in the same order they appear) and removed the repetitive search for the end of the list on sheet3 (putting the incrementer in the good item's Case block... Sub trythis() mc = 1 'Column A dlr = Sheets("sheet3").Cells(Rows.Count, "a").End(xlUp).Row + 1 For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 Select Case Cells(i, mc) Case Is = "c", "d", "e" 'Good items Cells(i, mc).Copy Sheet("sheet3").Cells(dlr, "a") dlr = dlr + 1 End Select Next i Columns(mc).Clear End Sub -- Rick (MVP - Excel) "Don Guillett" wrote in message ... Something like this Sub trythis() mc = 1 'column A For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 dlr = Sheets("sheet3").Cells(Rows.Count, "a").End(xlUp).Row + 1 Select Case Cells(i, mc) Case Is = "a", "b" Rows(i).Delete Case Is = "c", "d", "e" Cells(i, mc).Copy Sheet("sheet3").Cells(dlr, "a") Rows(i).Delete Case Else End Select Next i Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "randy.buchholz" wrote in message ... Hi Group, I'm trying to work with some log files and have been directed to do this in Excel. I'm mostly a DB guy and usually only use Excel 2007 for preping data for a DB load, I need your help please. The log files are just lines in a text file. In excel I have one column with a row for each log entry. I need to perform two processing steps on this data, both using something like a "Select where in" SQL function. I have two lists. The first list has "good" items like "Record Inserted". The Second list is a list of "bad" key words; like "Error" or "Warning. What I am trying to do step throught the column, processing each row. (I know how to navigate, but the processing is the issue) The rules for each row/cell a 1) If the cell value exactly matches any "Good" value, the row is deleted. 2) If the cell contains any of the words in the "Bad" list, the cell content is moved (to another sheet?) and the row is deleted from the log. These rules are applied is this order. Remaining rows are untouched. Any help would be appreciated, Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for "Select where in" type function
Thanks guys you're awesome.
"randy.buchholz" wrote in message ... Hi Group, I'm trying to work with some log files and have been directed to do this in Excel. I'm mostly a DB guy and usually only use Excel 2007 for preping data for a DB load, I need your help please. The log files are just lines in a text file. In excel I have one column with a row for each log entry. I need to perform two processing steps on this data, both using something like a "Select where in" SQL function. I have two lists. The first list has "good" items like "Record Inserted". The Second list is a list of "bad" key words; like "Error" or "Warning. What I am trying to do step throught the column, processing each row. (I know how to navigate, but the processing is the issue) The rules for each row/cell a 1) If the cell value exactly matches any "Good" value, the row is deleted. 2) If the cell contains any of the words in the "Bad" list, the cell content is moved (to another sheet?) and the row is deleted from the log. These rules are applied is this order. Remaining rows are untouched. Any help would be appreciated, Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What Function Should I Use. Is this a "What If" Type of Argument? | Excel Worksheet Functions | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
how do I type "itis" without Excel putting a space "it is"? | Excel Worksheet Functions | |||
"Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
Where is the toolbar with the "bold type", "font type", options | New Users to Excel |