Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 18th 06, 09:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 1
Default 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   Report Post  
Old September 19th 06, 02:39 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,203
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting a worksheet but retaining values from the worksheet. [email protected] Excel Discussion (Misc queries) 1 September 13th 06 03:00 PM
Deleting a worksheet but retaining values from the worksheet. [email protected] Excel Discussion (Misc queries) 1 September 13th 06 02:48 PM
Deleting rows with specific values whatzzup Excel Discussion (Misc queries) 1 July 6th 06 10:35 AM
Unique identifier Steve Barnett Excel Discussion (Misc queries) 19 January 6th 06 11:26 AM
Unique identifier Steve Barnett Excel Worksheet Functions 18 January 6th 06 11:26 AM


All times are GMT +1. The time now is 11:44 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017