Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Please help with removing duplicates with vba

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Please help with removing duplicates with vba

    Hello all,

    This is my first post and I have been using this website for a few months now, but never officially. Today is my first official post. I would like to thank all that have helped me so much so far, without knowing. This site is truly a gem. Anyway, I have a problem that I can't seem to figure out. I'm really new with macros and I have yet to create something from scratch. I have been able to get by so far by putting things together that I have seen from other people. This is what I have and I would really appreciate any help. I hope it posts correctly. I have, on the left side series x with values 1 2 3 4 5 6 and it repeats itself a few times. I have series Y with certain values 1 2 3 etc and finally Z with similar values. Both Y and Z can repeat. How can I create a macro say for Column B to take those series in column B and remove the duplicates of the series X, Y and Z leaving only one of each series with one set of their relative values for Column C? Thank you so much in advance for the help.
    Code:
    X		X
    1		1
    2		2
    3		3
    4		4
    5		5
    6		6
    Y		Y
    1		1
    2		2
    3		3
    4		4
    Z		Z
    1		1
    2		2
    X		
    1		
    2		
    3		
    4		
    5		
    6		
    Y		
    1		
    2		
    3		
    4

  2. #2
    New Member
    Join Date
    May 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    sorry, I couldn't figure out how to edit my post, but I made a mistake. how can I make it so that the duplicates rows are deleted and i'm left with what's in the second column of the original post. my apologies. by the way, how does one edit their posts? I couldn't find the edit button on the page. could it be that i'm using a tablet?

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    so sorry but I'm still not able to figure this out. I'm bumping it in hopes that it gets noticed. this is in excel 2013

  4. #4
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    Quote Originally Posted by tolga View Post
    so sorry but I'm still not able to figure this out. I'm bumping it in hopes that it gets noticed. this is in excel 2013
    . Welcome to the Board...
    . Do not Bump too soon. And avoid , if you can posting corrections.
    . The board is quite busy and many of the best members that can help the most start their day by checking zero Reply posts.
    . So you lose that chance if you reply yourself.
    . I just answered something similar to your Thread .
    . And I had your Thread as one to check out later, if I get the time, and I will do it.
    . in the meantime ......... get clued up on FAX forum Rules etc. but most importantly...



    Try and prepare a clear “Before” and “After” “Picture”


    . To do that you can either
    . 1) - post two screenshots ( see notes for how to do that in my signature –Please do not post am image as we cannot copy that to a spreadsheet!)
    . or
    . 2) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

    .3 ) – attach two files. ( or better, one File with two sheets, - a “Before” and an “After”.. . For example send over this free thing: Box Net,
    Remember to select Share after uploading and give us the link they provide.
    ………..
    . Make sure there is just enough data to make it clear what is needed, so reduce to a maximum of about 40 rows. Remember to desensitize the data – make the data up if you like, as long as it is representative of all possible scenarios and data types.
    . - So the “Before” should have just your initial data.
    . - The “After”, hand written by you should show exactly how it should look as a result of a Formula or any code based on the exact sample data you give in the “Before


    Alan

  5. #5
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    P.s. Here are the notes i had im my signature -I forgot i changed them last night just before the Bear "got it"...

    _______________________________________________________________________________________________________

    Google first with: site:MrExcel.com "Short Title or Theme of wot you want"
    Use Code Tags: Highlight code; click on the # sign at the top of the thread window, or use VB Code HTML Maker and Spreadsheet HTML Maker: Ask Mr. Excel - VBA HTML Maker ; MrExcel HTML Maker
    ; https://onedrive.live.com/?cid=8cffd...CE27E813%21189
    Post Range Screenshots (Win & Mac)- see bottom page here https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    XL 2003 2007 2010

  6. #6
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    Togla,
    . I really cannot make head or tail of wot you want.
    . As you said yourself you made a Bl### mess..
    . Do what I said and practice posting in the test area
    Test Here

    . Post as much crap there as you want, it don't matter - all posts there are ignored and get deleted regularly.

    . practice at the very least to post a simple Table.. I told you how...

    . Or if you are really not capable , as it is your first time , send me a PM ( Private Message ). I will then give You my Email and You can drop me off a File. Don’t paste any Emails ( ESPECIALLY MINE ) in this Thread. – It ‘aint allowed!!

    BUT
    I ‘aint going to waste my time unless You give me a clear „BEFORE“ and “AFTER” as I explained before

    Alan

    P.s.

    To PM me. You must be logged in. Then click on my name above the Picture of me F###### the Bear. Then select Private message.. the rest should be simple..

  7. #7
    New Member
    Join Date
    May 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    Quote Originally Posted by DocAElstein View Post
    Togla,
    . I really cannot make head or tail of wot you want.
    . As you said yourself you made a Bl### mess..
    . Do what I said and practice posting in the test area
    Test Here

    . Post as much crap there as you want, it don't matter - all posts there are ignored and get deleted regularly.

    . practice at the very least to post a simple Table.. I told you how...

    . Or if you are really not capable , as it is your first time , send me a PM ( Private Message ). I will then give You my Email and You can drop me off a File. Don’t paste any Emails ( ESPECIALLY MINE ) in this Thread. – It ‘aint allowed!!

    BUT
    I ‘aint going to waste my time unless You give me a clear „BEFORE“ and “AFTER” as I explained before

    Alan

    P.s.

    To PM me. You must be logged in. Then click on my name above the Picture of me F###### the Bear. Then select Private message.. the rest should be simple..

    hi doc! here it is wish me luck! I hope I explained it right. worked in test and it's awesome! thanks for the tips!

    before: you have type1, type2 etc. the word type indicates that it's the start of a series. the 1 and the 2 are unique type values that contain various items like apples, oranges, pears, bear and lube.

    type1
    apples
    oranges
    pears
    type2
    apples
    apples
    apples
    type1
    bear
    type2
    lube

    after: only the first series of the unique types are left with their relative contents and duplicate types with their contents are taken out. so type1 and type2 are gone and so are the bear and the lube. thank goodness for that! duplicates are disregarded as far as contents within types. so basically I need first "types" and their contents out of a given column. let's say column c or something. what to do and how to do? (with excel 2013 vba)

    type1
    apples
    oranges
    pears
    type2
    apples
    apples
    apples

  8. #8
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    Hi,

    Lesson 2 in Posting at Mr Excel

    . It is a good idea usually to reply with the „Reply With Quote“ Button . That way everyone keeps track of who is talking to who. ( Some Board Regulars do not do that and it is a pain sometimes keeping track of who is answering who and in / to which Post. So you should usually Always do it and always include a bit of the original message, ….. But only enough to make it clear who you are talking to and about what – so shorten it to the minimum necessary. Otherwise you clutter up the thread again with crap. The mods are all much too busy these days doing the real work to tidy your mess up )

    . so for example..

    This would have done.. ( you simply chop bits out )


    [QUOTE=DocAElstein;4169737]…….
    . I really cannot make head or tail of wot you want…..
    . …… practice posting in the test area
    [URL="http://www.mrexcel.com/forum/test-here/"]Test Here[/URL]
    …….
    I ‘aint going to waste my time unless You give me a clear [B]„BEFORE“[/B] and [B]“AFTER”[/B] as I explained before……...[/QUOTE]

    …. Then I reply to Yous as follows

    [QUOTE=tolga;4169864].....worked in test and it's awesome!......

    before: you have type1, type2 etc. the word type indicates that it's the start of a series. the 1 and the 2 are unique type values that contain various items like apples, oranges, pears, bear and lube.
    ...........................


    after: only the first series of the unique types are left with their relative contents and duplicate types with their contents are taken out. so type1 and type2 are gone and so are the bear and the lube. thank goodness for that! duplicates are disregarded as far as contents within types. so basically I need first "types" and their contents out of a given column. let's say column c or something. what to do and how to do?....[/QUOTE]

    Which comes out in the post like this…. And then I start to answer..

    Quote Originally Posted by tolga View Post
    .....worked in test and it's awesome!......

    before: you have type1, type2 etc. the word type indicates that it's the start of a series. the 1 and the 2 are unique type values that contain various items like apples, oranges, pears, bear and lube.
    ...........................


    after: only the first series of the unique types are left with their relative contents and duplicate types with their contents are taken out. so type1 and type2 are gone and so are the bear and the lube. thank goodness for that! duplicates are disregarded as far as contents within types. so basically I need first "types" and their contents out of a given column. let's say column c or something. what to do and how to do?....
    ….

    . So here it comes, My answer / response…

    . This sounds a strange requirement .. just taking the first occurrence of any type and just ignoring further types and their content.. I mean it’s harmless fun with the Bear, and , honestly I’m sure he enjoys it. And it is only because the wife is away for a few days….


    . This problem sounds very easy from the VBA side. It is probably a case of getting your data structured carefully. I hope you are sure of your requirement, and do not, for example, want a list of all Unique types with all the sort of contents that could be in them gathered from you entire list. (so then you would want the Bear and the lube in it’s appropriate Unique list in the output ) - The Bear will be nervous, if that is the case, as it is then not a case of modifying the following code, but a case of starting from scratch, wasting any effort getting the following code. - The Bear may suffer then….
    …………..
    ………………………………………………..
    Anyways

    . This code does basically what you asked for in your Before and After. ( For no particular reason I have input (Before ) in Column A and Output (After ) in column G.

    . You start like this:

    Using Excel 2007
    -
    A
    B
    1
    2
    type1
    3
    apples
    4
    oranges
    5
    pears
    6
    type2
    7
    apples
    8
    apples
    9
    apples
    10
    type1
    11
    bear
    12
    type2
    13
    lube
    14
    Toga



    . This is the final result you get.. ( I took the liberty of including a header row, as this is often a good idea with VBA and Excel stuff )

    Using Excel 2007
    -
    A
    B
    C
    D
    E
    F
    G
    1
    In Out
    2
    type1 type1
    3
    apples apples
    4
    oranges oranges
    5
    pears pears
    6
    type2 type2
    7
    apples apples
    8
    apples apples
    9
    apples apples
    10
    type1
    11
    bear
    12
    type2
    13
    lube
    Toga


    Code:

    Code:
    Sub ReorgTogas()
     
    '1)  Some Initial Object setting, variables and initial data capture too Array- usin VBA Array mehtod, minimising interaction with spreadsheet
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Toga") 'Give abreviation method, properies etc. of worksheets object obtained by typing .
    Dim arrIn() As Variant: Let arrIn() = ws.Range("A1").CurrentRegion.Value 'Dynamic Array for "Capture" of Spreadsheet, using the VBA allowed "one liner" to assign an Array to values of cells in a range. So must be a variant as it sees the Range object as it is assigned a collection
    Dim arrOut() As String 'This is a non Dynamic array to have values asigned in the following loop, so we can define it#s type. Here String ic conveniant for names and numbers.
    ReDim arrOut(1 To (UBound(arrIn(), 1)) - 1, 1 To 1) 'Must give the output Array a size or we cannot use it later. For now, make it maximuum possible size it could be. Must use reDim as Dim only takes numbers, not variables
       
    '2)   Use Dictionary (Keys only ( var=.item  method  ) ) to get eunuch values (keys) of VlookUp Column A----------------------------------------
    '  For "Early binding"--requires library reference to MS Scripting Runtime - Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime
    '  ..Or crashes at next line.....---
    'Dim dicLookupTable As Scripting.Dictionary 'Data held with a unique "Key"or Part Number.
    'Set dicLookupTable = New Scripting.Dictionary
    ' The next two lines are an alternative called Late binding. (But note some Dictionary methods and properties
        Dim dicLookupTable As Object
        Set dicLookupTable = CreateObject("Scripting.Dictionary")
    ' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense
    ' will then work for the Microsoft Scripüting Runtime stuff and give you suggestions after you type the .dot thing
        dicLookupTable.CompareMode = vbTextCompare 'Not quite sure wot this does yet
           
    Dim runc As Long 'Loop Bound Variable (Count)  http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
     '   The method =.Item() works in a nice way that allows us to make unique keys without assigning items    http://www.snb-vba.eu/VBA_Dictionary_en.html
     '   -- Usually the method .Item() is used to assign an item of some unique key to a vaiable.  z = dicLookupTable.Item(x(i).  If the key does not exist then it is  made...convenient   ehh?--- ( and no value will be given to the variable )
        Dim z As Variant 'Subtle dimensioning reason... in method =.Item() z becomes empty "" "No value is given??" snb??  Post #12 http://www.excelforum.com/excel-programming-vba-macros/1083899-copy-and-paste-entire-row-to-second-sheet-based-on-cell-value.html
            For runc = LBound(arrIn()) + 1 To UBound(arrIn()) 'Start looking down column at row 2 so as not to get the heading is first dic item
            If Left$(arrIn(runc, 1), 4) = "type" Then 'check for a type heading in list
                z = dicLookupTable.Item(arrIn(runc, 1)) 'You will not see anything here: Post #7   http://www.excelforum.com/excel-programming-vba-macros/1083899-copy-and-paste-entire-row-to-second-sheet-based-on-cell-value.html
                Else 'Redundant Code - assign no key to things in a type list
                End If
            Next runc
        Dim Eunuchs() As Variant: Let Eunuchs() = dicLookupTable.keys ' The unique keys are put into a 1 Dimensional Dynamic array called zz. Probably again the variant is needed as it sees the Dictionarry object initially, the usual "one liner" type assignment
                '    Dim rResults() As Variant: Let rResults() = dicLookupTable.Items() 'Extra line helpful to examine items in watch window... as dicLookupTable in watch window just the keys!! and a limited number thereof  http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-2.html
    'End of Part 2 initial set up Of MRSD and use of keys to get unique values ---------------------
     
    '3 ) Main Looping to make Array for output..
     
    Dim rws As Long, runcs As Long, rout As Long: Let rout = 0 ' "Rows" of Input Array, rows within euniques list, and "rows" of output Array,
        For runc = LBound(Eunuchs()) To UBound(Eunuchs()) 'take each unique List type
            For rws = 2 To UBound(arrIn(), 1) 'go down input "row"
                If arrIn(rws, 1) = Eunuchs(runc) Then
                    Let rout = rout + 1 'goto next empty space in Output Array and
                    Let arrOut(rout, 1) = arrIn(rws, 1)
                        For runcs = 1 To 234 '234 is arbritrary number - change to maximim possible items in a unique List if you know it.
                            If Not dicLookupTable.Exists(arrIn((rws + runcs), 1)) Then 'If the next roow is not a unique list
                            Let rout = rout + 1 'goto next empty space in Output Array and
                            Let arrOut(rout, 1) = arrIn(rws + runcs, 1)
                            Else
                            GoTo NextrEunich 'We have hit a List type heading so we stop adding to the rows of the output array...and..
                            End If '  ... go and...
                        Next runcs '  look for the next eunich List type
                Else 'redundant code: have not found a List type yet
                End If
            Next rws
    NextrEunich: '"Sprung point" to come to once a Unique type with all contents has been put in Output Array
        Next runc
       
    '4) Output to  sheet
    Let ws.Range("A1").Resize(1, 7).Value = Array("In", , " ", , , , "Out") 'A typical step that looks cleverer then it is, I resize first cell to a range including all headings I want, and then VBA lets me assign the values in a (Heasding here)  Array to the cells in a simplw = step
    Let ws.Range("G2").Resize(UBound(arrOut(), 1), 1).Value = arrOut() 'Similat to the above just convenient to resize to size of Arrray i am actually outputing ( Assuming there are more than one value of the Uniques then I output empty values also, but that is useful as it actually clears those cells in case they had any in from a last run where there were more in the output Array
     
    End Sub 'ReorgTogas()
    . I fear you will be coming back with questions….( such as: “??? I have no idea how to run a macro” …;. or “I got my requirement wrong.”. ..;.. or “it would not work because my actual data is.”. etc, etc.. )

    . Be patient.. I am automatically informed of your Posts here. But I just do this for fun in my spare time, like most of us here, and I may be “off” for a while…


    . In the unlikely event that you do not have any questions and everything works perfectly as you want then it would be nice if you let me know ..

    . Alan

    ( Of course I may well have not got the code 100% bullet proof, - that’s is not possible at this stage with limited data etc.. )

  9. #9
    New Member
    Join Date
    May 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    Quote Originally Posted by DocAElstein View Post


    . I fear you will be coming back with questions….( such as: “??? I have no idea how to run a macro” …;. or “I got my requirement wrong.”. ..;.. or “it would not work because my actual data is.”. etc, etc.. )

    . Be patient.. I am automatically informed of your Posts here. But I just do this for fun in my spare time, like most of us here, and I may be “off” for a while…


    . In the unlikely event that you do not have any questions and everything works perfectly as you want then it would be nice if you let me know ..

    . Alan

    ( Of course I may well have not got the code 100% bullet proof, - that’s is not possible at this stage with limited data etc.. )

    thank you so much doc. this totally works. I really appreciate all your help and guidance.

  10. #10
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Please help with removing duplicates with vba

    Quote Originally Posted by tolga View Post
    thank you so much doc. this totally works. I really appreciate all your help and guidance.

    Yous welcome..
    Thanks for letting me know
    Alan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com