Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help Needed: Comparing cell values and deleting rows
Greetings Excel Pros!
I am new to Macro writing... I know what I want to do, but given the time constraints, I think this medium is the quickest solution to my question. I don't have enough time to spend researching and studying VBA syntax and built-in functions. Here's the problem to be solved with a Macro: I have a large file (~ 25,000 row entries) but upon filtering, I can isolate blocks of about 60 rows (not concurrent, i.e. rows 1, 2, 13, 18, 20, 25, 31 etc.). Within this sub-set I want to delete rows based on a comparison task. Columns A-M are in use, and rows 1 & 2 are frozen because I'm using them as header rows. I will sort Column "L" so that its entries contain values Lot 1 - Lot 4 in ascending order. Column "D" contains the values to be compared with all row entries where Column "L" contains Lot 1 - Lot 3. The main idea is to delete duplicates (based only on value of Column "D" entry) for on Lot 4 entries. Additionally, when conducting the check, only the first THREE characters of each Column "D" cell should be compared. Only the first FIVE characters of each Column "L" cell should be compared. I know that in the spreadsheet there is a "=LEFT(A:A, 5)" function for example. Can this be used here somehow? Here's the pseudocode I came up with... 'Check Column L for first occurrence of "Lot 4" in order to assign reference For i = first_row_of_column_L To last_entry_found_in_column_L Step +1 If current_cell = "Lot 4" Then init_Lot4_Ref = current_cell 'Break at this point, our reference is set! 'Else, check next cell Next i 'Now that we have a reference, check Column D to delete duplicate rows For j = init_Lot4_Ref to last_entry_found_in_column_L Step +1 For k = first_row to j Step +1 If Cells(k, "D").Value = Cells(j, "D").Value Then Rows(k).EntireRow.Delete Next k Next j *** Remember that when checking Column L entry, only check first FIVE characters (some entries are listed "Lot 4 special" but this should be treated like "Lot 4"). When checking Column D entry, only check first THREE characters (some entries are listed "225(NEW)" but this should be treated like "225"). Thanks to whomever can help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help Needed: Comparing cell values and deleting rows
In VBA there are similar functions to LEFT() and RIGHT() used on a worksheet.
Keep in mind that within VBA, case is evaluated, so "Lot 4" < "LOT 4", this example code would examine the first 5 characters of each of 2 cells, making sure that leading spaces were removed and that both were in UPPER case before making the comparison: If UCase(Left(Trim(Cells(k, "D")), 5)) = UCase(Left(Trim(Cells(j, "D")), 5)) Then .... use 3 instead of 5 for the other test. Hope this helps you get moving again. " wrote: Greetings Excel Pros! I am new to Macro writing... I know what I want to do, but given the time constraints, I think this medium is the quickest solution to my question. I don't have enough time to spend researching and studying VBA syntax and built-in functions. Here's the problem to be solved with a Macro: I have a large file (~ 25,000 row entries) but upon filtering, I can isolate blocks of about 60 rows (not concurrent, i.e. rows 1, 2, 13, 18, 20, 25, 31 etc.). Within this sub-set I want to delete rows based on a comparison task. Columns A-M are in use, and rows 1 & 2 are frozen because I'm using them as header rows. I will sort Column "L" so that its entries contain values Lot 1 - Lot 4 in ascending order. Column "D" contains the values to be compared with all row entries where Column "L" contains Lot 1 - Lot 3. The main idea is to delete duplicates (based only on value of Column "D" entry) for on Lot 4 entries. Additionally, when conducting the check, only the first THREE characters of each Column "D" cell should be compared. Only the first FIVE characters of each Column "L" cell should be compared. I know that in the spreadsheet there is a "=LEFT(A:A, 5)" function for example. Can this be used here somehow? Here's the pseudocode I came up with... 'Check Column L for first occurrence of "Lot 4" in order to assign reference For i = first_row_of_column_L To last_entry_found_in_column_L Step +1 If current_cell = "Lot 4" Then init_Lot4_Ref = current_cell 'Break at this point, our reference is set! 'Else, check next cell Next i 'Now that we have a reference, check Column D to delete duplicate rows For j = init_Lot4_Ref to last_entry_found_in_column_L Step +1 For k = first_row to j Step +1 If Cells(k, "D").Value = Cells(j, "D").Value Then Rows(k).EntireRow.Delete Next k Next j *** Remember that when checking Column L entry, only check first FIVE characters (some entries are listed "Lot 4 special" but this should be treated like "Lot 4"). When checking Column D entry, only check first THREE characters (some entries are listed "225(NEW)" but this should be treated like "225"). Thanks to whomever can help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting a worksheet but retaining values from the worksheet. | Excel Discussion (Misc queries) | |||
Deleting a worksheet but retaining values from the worksheet. | Excel Discussion (Misc queries) | |||
Deleting rows with specific values | Excel Discussion (Misc queries) | |||
Unique identifier | Excel Discussion (Misc queries) | |||
Unique identifier | Excel Worksheet Functions |