Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    if not found -delete entire row (97+)

    I have a large spreadsheet ---sheet 1 has about 25,000 rows and about 10 columns
    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

  2. #2
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if not found -delete entire row (97+)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: if not found -delete entire row (97+)

    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)

    add a column in sheet2:
    =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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: if not found -delete entire row (97+)

    <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

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if not found -delete entire row (97+)

    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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: if not found -delete entire row (97+)

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: if not found -delete entire row (97+)

    Not sure what best approach is if done with macro:

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

    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

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if not found -delete entire row (97+)

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

  9. #9
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if not found -delete entire row (97+)

    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: if not found -delete entire row (97+)

    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>


  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: if not found -delete entire row (97+)

    It doesn't, never mind.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if not found -delete entire row (97+)

    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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: if not found -delete entire row (97+)

    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

  14. #14
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if not found -delete entire row (97+)

    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: if not found -delete entire row (97+)

    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 LastLast

Posting Permissions

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