sheet 2 has about 150 entries in column A only---ie 150 rows.If the first four digit combination of Sheet1 Column A exists in sheet 2 column1 then all rows in sheet 1 starting with those four digits remain if 4 digits not found in sheet 2 then all rows in sheet 1 which cannot be matched in sheet 2 shall be deleted .
Example
sheet 1 --- a ------------- ----- b ---------- c
---------- 0123.234--------- ghh ------kkkk
----------- 0123.4567--------- hjj------- kjhj
----------- 2222.677--------- -jjj
---------2222.666-------------kkk
---------- 2345.667------------ hhu-----kkkk
----------2444.777------------- jjjjj------kkkk
sheet 2 a
------------- 0123
------------- 2444
In this example all rows in sheet1 starting with 22 or 23 shall be deleted.
I managed to write a macro but it was so slowwwwwwwww because I looped row by row---BTW column A in both sheets sorted in ascending order.
Hope I have made myself clear--
TIA
Smbs

Steve -- I'm afraid I might be doing somehing wrong but I get "true " all along - match or no match, pls could u check it out again--even tried on small sheet
Thanx
Smbs

In your example dataset, you will get all TRUE if the items in sheet2 are numbers not text (if the items in sheet 2 were text, you would get a FALSE for "2444.777" even if the sheet1 were numbers)

=text(A1,"0000")
and datafill it in
copy - paste special values
compare the list in sheet 1 to this column.
[Note: you might have to do the same to sheet 1 to ensure that all of them are text]

By doing the left4 you are making it a string, so you must compare strings. i assumed you had strings since you had leading zeroes, though you could get this from formatting. MATCH does not look at the formatting, but the cells contents. "2444" <>2444

Steve

Steve

<P ID="edit" class=small>(Edited by sdckapr on 13-Nov-03 16:11. Messed up TRUE/FALSE and corrected)</P>How about a non-macro version?
In a blank col of sheet 1 (col d?) add this formula in row 1
<pre>=ISNA(MATCH(LEFT(A1,4),Sheet2!\$A\$1:\$A\$150,0)) </pre>

Datafill it down the column
The formula will yield TRUE (if there is NOT a match) and FALSE if there is a match

Put on data-autofilter, filter on FALSE, select the rows and delete

You could even have a macro recreate the steps. Doing it manually allows you to view the list before deleting and allows undo.

Steve

Steve!
Gotit changed all to text format--works great. For interests sake after having tried to do it by macrowhich works very slowly could u cook up one for my education (if u have the time) .
Thanks again
Smbs

Try this approach on a test workbook:

Sub DelCriteriaRows()
With ActiveSheet.UsedRange ' change this reference to suit the range
.AutoFilter Field:=1, Criteria1:=">=2200", Operator:=xlAnd, Criteria2:="<2400"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
End Sub

Not sure what best approach is if done with macro:

Like I suggested with match and autofilter
could be done with advanced filter also

Another faster macro might be to read your data set into an array, then read the smaller set into an array. then copy array items into new array that "match", your condition, then "dump" the new array into a workbook. I actually think this might be relatively fast. It would also allow keeping the original and creating 2 new lists, matching an non-matching.

Doing the macro for the way I suggested, if you want the education, would be to record the steps you do, and look at the code (this is where I would start if I were to do it)

Steve

Thanx John and Steve --will try both suggested ways and let u both know.
Smbs

Hi John
About to try your suggested macro --dont understand to much yet but I don't see any reference to matching the first 4 digits/letters only of the contents of sheet1column1---Am I missing something?
Thanx
Smbs

Try this routine (with some functions). It should be faster than your method.

The routine (DeleteNotFound):
1)gets col A of Sheet1 and Sheet2 and stores them in an array (uses the function GetArrayA).
2) it then loops thru the items in Array1 and looks for the first 4 characters in Array2 (Using IsFound Function)
3) If it is NOT found it "combines" with UNION the cell from col A of that row, with whatever was currently marked. (using myUnion function)

At the end of the loop the rng is "all the cells that do not match"
I then (for testing) have the entire rows with the rng colored yellow. if working, you can uncomment the line and it run the delete.

Steve

<pre>Option Explicit
Sub DeleteNotFound()
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim vArray1
Dim vArray2
Dim x As Long
Dim rng As Range

Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")

vArray2 = GetArrayA(wks2.Name)
vArray1 = GetArrayA(wks1.Name)

For x = 1 To UBound(vArray1)
If Not (IsFound(Left(vArray1(x, 1), 4), vArray2)) Then _
Set rng = MyUnion(rng, wks1.Cells(x, 1))
Next

'do this to test will highlight rows to be deleted
rng.EntireRow.Interior.Color = vbYellow
'remove comment after if working
'rng.EntireRow.Delete
End Sub

Function GetArrayA(sWksName As String)
'Fills an array by grabbing a range from a worksheet
'sRngName is a rangename from the sheet
'lStartRow is the starting row
'iStartCol and iEndCol are the start and ending columns
'The last row of the data is determined at runtime
'by finding the last cell in the first column

Dim vArray
Dim wks As Worksheet
Set wks = Worksheets(sWksName)
wks.Select
vArray = wks.Range(Range("A1"), Range("A65536").End(xlUp))
Set wks = Nothing
GetArrayA = vArray
End Function

Function IsFound(vItem, vArray) As Boolean
Dim x As Long
IsFound = False
For x = 1 To UBound(vArray)
If vItem = vArray(x, 1) Then
IsFound = True
Exit Function
End If
Next
End Function

Function MyUnion(rng1, rng2) As Range
'This is an "expanded version" of Union
'It works if the first range is empty before starting

If rng1 Is Nothing Then
Set rng1 = rng2
Else
Set rng1 = Union(rng1, rng2)
End If

Set MyUnion = rng1
End Function</pre>

It doesn't, never mind.

thanx again to Steve and John---
To answer your question text or numbers well its basically numbers but as it a large part list which has to contain four digits before a period "." --if project name is say 340.8899.000 then a zero is required ie 0340.8899.0000 whatever-
Using Steves formula I had to reformat the numbers as text and then the it worked.
Steve and John thanx for your latest macros will give them a try and let you how long it takes.
Many thanx again
Smbs

If they are numbers and you want the numbers before the decimal, you could use INT function then the leading zero wouldn't matter.

You mentioned first 4 characters, implying that you wanted to work with text. INT might be a better way to compare if you just want to strip off the decimal part.

Steve

Steve your lastest function/macro works fine and quickly however I am not quite sure how to use the " int" function which u say might be easier a basically I am dealing in numbers even though there might be zero thrown in front of three digit part numbers
Thanx again
Smbs

If you keep both cols as numbers then just use instead of the current line:

<pre> If Not (IsFound(Left(vArray1(x, 1), 4), vArray2)) Then _
Set rng = MyUnion(rng, wks1.Cells(x, 1))</pre>

Use:
<pre> If Not (IsFound(int(vArray1(x, 1)), vArray2)) Then _
Set rng = MyUnion(rng, wks1.Cells(x, 1))</pre>

This will compare the integer portion of the value in sheet1 to the number in sheet2.

Just curious: Your macro did much the same thing, although it deleted directly in the worksheet as it went. About how long did it take and about how long does my macro take with the same set? (you said sheet1 about 25000 rows, sheet 2 about 150 rows). I didn't feel like generating a huge set to try it, I just like to learn the differences in speed by doing it different ways (esp with large datasets).

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•