+ Reply to Thread
Results 1 to 15 of 15

Mark duplicated or triplicated data-macro

  1. #1
    Registered User
    Join Date
    10-29-2011
    Location
    Zagreb
    MS-Off Ver
    Excel 2007
    Posts
    15

    Mark duplicated or triplicated data-macro

    Hi, i need to delete duplicated or triplicated data, but it's not that simple ..when i say duplicated or triplicated i mean rows of data that have 2 set out of 3 same values...for example..


    Please Login or Register  to view this content.
    In this example, data in 1., 2. and in 3. row are the same triplicated entities...and in some cases there are two or maximum 4 cases where the data in column A and B beetween the rows are same..it's the same unit, just the invate code (column C) is different...so the duplicated data is not only decided by one type of data(for ex. Series), but two out of three types of data that must be the same (Name and Series must be the same between the rows)

    the man who designed the database didn't include an option that a unit has more invite codes, he just created a new unit with same original data and a new invite code

    In the end, i need to somehow mark those rows of data..for ex. entire row in difrent color, or to write something in the next empty cell in that row index (column D), or to color that empty cell..doesen't matter, the point is that i can easily spot that data and manualy delete the ones i don't need...and it would help me because that duplicated data is not scattered across a sheet, they are one next to each other.

    Thank you
    Last edited by sky123; 11-07-2011 at 11:58 AM.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Mark duplicated or triplicated data

    Try to use this array formula (for the entry, press Ctrl+Shift+Enter) in column D
    Please Login or Register  to view this content.
    or none-array formula
    Please Login or Register  to view this content.
    Last edited by nilem; 11-07-2011 at 02:03 AM. Reason: edited formula

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Mark duplicated or triplicated data

    Given that you are using 2007 you could also use this in say D2.
    Please Login or Register  to view this content.
    If you want to highlight the result
    Select Columns A:C Conditional Formatting > Use a formula ...
    Please Login or Register  to view this content.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    10-29-2011
    Location
    Zagreb
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Mark duplicated or triplicated data

    Thank you, they all work great..but i forgot tho mention i need a macro, i can use these formulas..but a macro would be just a bit more convinient, and because i don't always have the same sized area (number of rows), the macro would have to select area (row) to the laste row with data. Is it posible?

  5. #5
    Forum Contributor
    Join Date
    11-04-2011
    Location
    pak
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Mark duplicated or triplicated data-macro

    Hello,
    Here is the VB code that might help you.
    rgrds

    Option Explicit
    Sub arrrr1()

    Dim arr1() As Variant
    Dim comarr() As Variant
    Dim list1 As Variant
    Dim complist As Variant
    Dim TheRange As Range
    Dim i, j, k, l, m, n As Long
    Dim upper As Long


    For i = 1 To 100

    If Cells(i, 1).Value = "" Then
    upper = i - 1
    Exit For
    End If
    Next



    ReDim arr1(2 To upper, 1 To 2)
    ReDim comarr(2 To upper, 1 To 2)
    Set TheRange = Range(Cells(2, 1), Cells(upper, 2))


    For j = 2 To upper
    For k = 1 To 2
    arr1(j, k) = Cells(j, k).Value
    comarr(j, k) = Cells(j, k).Value

    Next
    Next



    For l = 2 To upper

    m = l + 1

    For n = m To upper

    If (arr1(l, 1) = comarr(n, 1) And arr1(l, 2) = comarr(n, 2)) Then

    Cells(n, 2).Offset(0, 2).Value = "Repeated"

    End If
    Next
    Next


    End Sub

  6. #6
    Forum Contributor
    Join Date
    11-04-2011
    Location
    pak
    MS-Off Ver
    Excel 2007
    Posts
    132

    Talking Re: Mark duplicated or triplicated data-macro

    if you like my code please rate it

  7. #7
    Registered User
    Join Date
    10-29-2011
    Location
    Zagreb
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Mark duplicated or triplicated data-macro

    Hi, this code does the trick, but can you please tell me where in the code can i change the colums to compare (for example sometime i need to compare columns C and D), not always A and B? I tried to change certain values, but there is too much variables and loops to take take into account, or just rewrite the code to fit columns C and D, and i'll figure it out...thank you
    Last edited by sky123; 11-07-2011 at 10:05 PM.

  8. #8
    Forum Contributor
    Join Date
    11-04-2011
    Location
    pak
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Mark duplicated or triplicated data-macro

    try this, if have probelm then feedback
    Option Explicit
    Sub arrrr1()

    Dim arr1() As Variant
    Dim comarr() As Variant
    Dim list1 As Variant
    Dim complist As Variant
    Dim TheRange As Range
    Dim i, j, k, l, m, n As Long
    Dim upper As Long


    For i = 1 To 100

    If Cells(i, 1).Value = "" Then
    upper = i - 1
    Exit For
    End If
    Next



    ReDim arr1(2 To upper, 2 To 3)
    ReDim comarr(2 To upper, 2 To 3)
    Set TheRange = Range(Cells(2, 2), Cells(upper, 3))


    For j = 2 To upper
    For k = 2 To 3
    arr1(j, k) = Cells(j, k).Value
    comarr(j, k) = Cells(j, k).Value

    Next
    Next



    For l = 2 To upper

    m = l + 1

    For n = m To upper

    If (arr1(l, 2) = comarr(n, 2) And arr1(l, 3) = comarr(n, 3)) Then

    Cells(n, 2).Offset(0, 2).Value = "Repeated"

    End If
    Next
    Next


    End Sub

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Mark duplicated or triplicated data-macro

    See if you can modify this code, no loops or arrays required.
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    11-04-2011
    Location
    pak
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Mark duplicated or triplicated data-macro

    Hello Marcol
    it is wonderful script. still trying to understand , I am new to Excel VBA
    thanks

  11. #11
    Forum Contributor
    Join Date
    11-04-2011
    Location
    pak
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Mark duplicated or triplicated data-macro

    Hi Marcol,
    your script is wonderful very speedy, 10 times speedy than mine. however it alsoMarking some itmes as "duplicated" which appear first time. for instance I compared your result and my result in sheet3 attached, your result show different e.g. 6622 item is different. can you please guide
    thanks
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-29-2011
    Location
    Zagreb
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Mark duplicated or triplicated data-macro

    Very usefull scripts..but i must state that an option to mark only the rows where the data is repeated..is very usefull...because now i can use my another macro to find rows where it states "Repeated" and delete that enitre row, and i'll have the first instance of that row data intact.

  13. #13
    Registered User
    Join Date
    03-26-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Mark duplicated or triplicated data-macro

    Hi I'm new to VBa and I have a problem. I have 28,000 rows of data that are in Column A. In Column B I need to put "<-3> [SSM] (CSM)" next to all grouped cells that contain duplicate data of 5 or more for example. I need to do the same respectively with the chart below with 2s, 3-4s, & quantities of 5+ but I want to exclude/ignore 0 readings.
    Please Login or Register  to view this content.
    I have tried this code, but I can't get it to work:
    Please Login or Register  to view this content.
    Please show me how to edit my code to make this work. I have tried figuring out how to use "Call FlagTrip" between End With & End Sub but I can't get that to work either.

    Thanks
    Last edited by woodrrow; 03-26-2013 at 10:31 PM.

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Mark duplicated or triplicated data-macro

    woodrrow,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Registered User
    Join Date
    03-26-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Mark duplicated or triplicated data-macro

    Hi arlu, I apologize for not reading all of the forum rules first. I was in a hurry and just posted quickly after quickly glancing at them. I created a new thread. I'll go and re-read the rules. Thanks for taking the time to correct my mistake.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1