Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Remove/List Duplicates from SAP list (Excel 2000 >)

    Hi,
    I need assistance in getting this macro to work.

    In essence it must look for a duplicate record which is TRUE if the GRV and the TEXT field is in more that 1 row.
    EG:
    txt 5561 grv 700557
    txt 5562 grv 700557
    txt 5563 grv 700557
    txt 5561 grv 700557 (duplicate)

    This record must be copied to a new "Error Sheet" and deleted from the original list.

    If the GRV is less than 700000 or >= 800000 then the record must be ignored
    If the GRV is blank (null) then the record must be copied to a "Error Blank" sheet. (I've not started this code yet!!!)

    I've started with code, but I think I have some niggles in it as it is not running the copy/delete portion of the IF statement.

    Please help!

    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove/List Duplicates from SAP list (Excel 2000 >)

    Rudi, Rudi,

    Are you sure you're not asking this on Sal21's behalf? He always comes up with requests like these. And SAP! The most horrible software I have encountered in my life! <img src=/S/puke.gif border=0 alt=puke width=60 height=15>
    Still no Option Explicit! <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>
    Still selecting ranges unnecessarily! <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>

    The following assumes that you have already created sheets named Error Sheet and Error Blank, with the same header row as Sheet1. As always when deleting rows, the code loops backwards.

    Sub ListDuplBlank()
    'Search for Duplicates within GRV and Text Columns and create list in Error Sheet
    Dim wshSAP As Worksheet
    Dim wshError As Worksheet
    Dim wshBlank As Worksheet

    Dim rngList As Range
    Dim lngSAPRow As Long
    Dim lngErrRow As Long

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wshSAP = Worksheets("Sheet1")
    Set wshError = Worksheets("Error Sheet")
    Set wshBlank = Worksheets("Error Blank")

    Set rngList = wshSAP.Range("A1").CurrentRegion
    rngList.Sort Key1:=rngList.Range("K1"), Key2:=rngList.Range("I1"), Header:=xlYes

    For lngSAPRow = rngList.Rows.Count To 3 Step -1
    If Trim(rngList.Range("K" & lngSAPRow)) = "" Then
    ' GRV blank, copy to Error Blank sheet
    lngErrRow = wshBlank.Range("A65536").End(xlUp).Row + 1
    rngList.Rows(lngSAPRow).Copy Destination:=wshBlank.Range("A" & lngErrRow)
    rngList.Rows(lngSAPRow).EntireRow.Delete
    ElseIf rngList.Range("K" & lngSAPRow) >= 700000 And _
    rngList.Range("K" & lngSAPRow) < 800000 Then
    If rngList.Range("K" & lngSAPRow) = rngList.Range("K" & (lngSAPRow - 1)) And _
    rngList.Range("I" & lngSAPRow) = rngList.Range("I" & (lngSAPRow - 1)) Then
    ' Duplicate, copy to Error Sheet
    lngErrRow = wshError.Range("A65536").End(xlUp).Row + 1
    rngList.Rows(lngSAPRow).Copy Destination:=wshError.Range("A" & lngErrRow)
    rngList.Rows(lngSAPRow).EntireRow.Delete
    End If
    End If
    Next lngSAPRow

    ExitHandler:
    Set wshBlank = Nothing
    Set wshError = Nothing
    Set wshSAP = Nothing
    Set rngList = Nothing
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  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: Remove/List Duplicates from SAP list (Excel 2000 >)

    YOu mention 3 things and I am not sure if they are all the same "condition"

    Copying Duplicates to new sheet and deleting on original sheet
    "Ignoring" GRV is less than 700000 or >= 800000. I don't understand what you mean by "ignoring": does that mean to not "move" the duplicates that have GRVs outside this range or ignore in some other way?
    If the GRV is blank (null) then copy (Is that even if not a duplicate?)

    Coding-wise, I think an easy way (may not be that bad even manually...) is to use filtering with temp columns. Create the criteria, filter, then copy the items to the new sheet and delete from original. VB can use filtering so it could even be done with code (using autofiltering allows excel to the "heavy-lifting").

    I am not clear exactly what you are after so I haven't done any coding. It seems you want to move duplicates with GRV>=700000 and <800000 and also move any with blank (or null) GRVs. Is that it?

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove/List Duplicates from SAP list (Excel 2000 >)

    Hans,
    If I ever reach your level of expertise...I will...well thats still a dream.
    The thing is...I know what you are saying: ... Still no Option Explicit! Still selecting ranges unnecessarily! I know these things...but its a matter of training the brain to think in these ways.
    Sigh...

    What can I say but perfect...thanx for your effort and time in helping me.
    BIG <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> FOR YOU HANS!

    PS: I am learning and things are sinking in... be it slowly. I have noticed that I am applying things I picked up from the lounge by default (without thinking)...so it is taking root! (If thats any consolation!)
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove/List Duplicates from SAP list (Excel 2000 >)

    Please note that in your sample file, there is one cell in column I containing 5562 as text, while others contain it as a number. They are not seen as equal.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove/List Duplicates from SAP list (Excel 2000 >)

    No hassle...that was sample data. The code will run on actual SAP exported data!
    Cheers
    Regards,
    Rudi

Posting Permissions

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