Results 1 to 15 of 15

Modify VBA to get rid Duplicated String when Transpose-Index/Score & String associated to.

Threaded View

  1. #1
    Registered User
    Join Date
    10-14-2017
    Location
    Miami, Florida
    MS-Off Ver
    2016
    Posts
    83

    Modify VBA to get rid Duplicated String when Transpose-Index/Score & String associated to.

    Hi,
    I'm wondering if someone help me to modify the following Code?
    My workbook have two sheets "Records" and "Ouput",
    On Column A2:A37 The sheet Records has Alphanumeric String and
    on Column B2:B37 Has Scores (Alphanumeic as well)
    Note that some String from Col A and Scores Col B are duplicated
    what the code do:
    it get the Scores (Col B2:B37) and copy as unique score on Sheet Output Col M1:M17
    Then extract any String associated to each Score and copy next to it on same sheet Ouput Col O1:V17

    NOTE 1: I need to Paste but starting on Row M2 of Ouput?, is pasting in Row M1, and i can't find where to change it

    NOTE2: The code is working find but on Range N1:V17 is copying unwanted Duplicated String on Single Score Row, It does'nt matter if any other string appear multiples Times on Multiples Scores But Just once (per row).

    See bellow The Ouput with Unwanted Duplicated String that i need to get rid of (red string).


    Score ID __STRING
    40 -----> 15
    20 -----> 18 18
    17 -----> 03 10 06 07
    13 -----> 23 23 23
    12 -----> 14 16 30
    11 -----> 05 12
    9 ------> 32
    8 ------> 23
    3 ------> 34 29 34 29
    5 ------> 09 01
    4 ------> 21 11
    2 ------> 17 20
    1 ------> 33 26 08 04
    0 ------> 02 13 36 28

    The Ouput what i need with Unique String that belong to each score
    ===========================================
    Score ID___STRING
    40 -----> 15
    20 -----> 18
    17 -----> 03 10 06 07
    13 -----> 23
    12 -----> 14 16 30
    11 -----> 05 12
    9 ------> 32
    8 ------> 23
    3 ------> 34 29
    5 ------> 09 01
    4 ------> 21 11
    2 ------> 17 20
    1 ------> 33 26 08 04
    0 ------> 02 13 36 28




    Im using this code
    ============

    Sub MG28Sep00() 'Private Sub CommandButton1_Click)
    Dim Rng As Range, Dn As Range, n As Long
    Dim Dic As Object, Q As Variant, K As Variant, c As Long
    With Sheets("records")
    Set Rng = .Range(.Range("B2:B37"), .Range("B" & Rows.Count).End(xlUp).Offset(1))
    End With
    Set Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
    For Each Dn In Rng
    If Not Dic.Exists(Dn.Value) Then
        ReDim ray(1 To Rng.Count)
        ray(1) = Dn.Offset(, -1) 'WAS -1
        Dic.Add Dn.Value, Array(ray, 1)
    Else
        Q = Dic.Item(Dn.Value)
            Q(1) = Q(1) + 1 'WAS +1
        Q(0)(Q(1)) = Dn.Offset(, -1).Value 'WAS -1
      Dic.Item(Dn.Value) = Q
    End If
    Next
    
    For Each K In Dic.keys
        With Sheets("output")
            c = c + 1 'WAS +2 Here control empty Row Down between, example If Set +2, display 1 row w data other empty, with +1 no empty rows
            .Cells(c, "M") = K
            .Cells(c, "N").Resize(, Dic.Item(K)(1)) = Dic.Item(K)(0)
        End With
    Next K
    End Sub

    Thanks a Millions !!!!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] String of Text into Box Score Format (Baseball Data)
    By windowshopr in forum Excel General
    Replies: 16
    Last Post: 07-06-2018, 12:44 AM
  2. Replies: 1
    Last Post: 08-07-2015, 04:01 PM
  3. [SOLVED] count duplicated string in Range for each letter,Non Adjacent Row
    By david gonzalez in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-25-2014, 04:00 PM
  4. Replies: 23
    Last Post: 05-19-2014, 09:45 PM
  5. Need to output to serial port if string in column is duplicated.
    By MikeHaras in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-23-2013, 03:45 AM
  6. [SOLVED] Returning Match Score From A String
    By clattenburg cake in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2013, 01:38 PM
  7. [SOLVED] VBA Search for string, modify and re-enter
    By passman86 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2013, 10:46 AM

Tags for this Thread

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