+ Reply to Thread
Results 1 to 3 of 3

Copy multiple ranges & paste but Sorted and only Unique Distinct values, skip blank cells

Hybrid View

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

    Copy multiple ranges & paste but Sorted and only Unique Distinct values, skip blank cells

    Hi,

    If someone know a tread to please let me know.

    Please I need vba code or udf to copy from columns T5,U5,V5,W5 (10,000 rows each column) to Correspondent Columns starting with Column Y5, then on Z5, AA5, AB5
    with following condition:
    -.Paste at Top (Starting in Y5,Z5,AA5,AB5)
    -.Only cell with data (skip blank cell)
    -.Digit on Sort Order
    -.Unique Distinct digits

    Example of Unique Distinct digits:
    Cell T13 -> 2,2,2,1,1
    Cell T15 -> 1,2,2,2,1
    Cell T20 -> 2,1,2,1,2
    This 3 cell Values Are equivalent to sorted Value on Cell Y10 -> 1,1,2,2,2

    Another example:
    Cell U6 -> 4,3,1,1,3
    Cell U15 -> 3,3,4,1,1
    Cell U22 -> 1,4,3,1,3
    Cell U23 -> 4,1,3,3,1
    The 4 cell Values Are equivalent to sorted Value on Cell Z5 -> 1,1,3,3,4

    Thank you !!!!!!!!!!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy multiple ranges & paste but Sorted and only Unique Distinct values, skip blank ce

    Sub Unique_Sorted_Digits()
        
        Dim v, s, r&, c&, i&, j&, k&
        
        v = Range("T5:W" & Columns("T:W").Find("*", [T1], , , 1, 2).Row)
        
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For c = 1 To UBound(v, 2)
                For r = 1 To UBound(v, 1)
                    If InStr(v(r, c), ",") Then
                        s = Split(v(r, c), ",")
                        For j = 0 To UBound(s) - 1
                            For k = j + 1 To UBound(s)
                                If s(j) > s(k) Then: Temp = s(k): s(k) = s(j): s(j) = Temp
                            Next k, j
                        .Item(Join(s, ",")) = ""
                    End If
                Next r
                
                Range("Y5").Offset(0, c - 1).Resize(.Count).Value = Application.Transpose(.Keys)
                .RemoveAll
                
            Next c
        End With
        
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

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

    Re: Copy multiple ranges & paste but Sorted and only Unique Distinct values, skip blank ce

    Hi Mr AlphaFrog,

    your VBA code work perfect!!!!!!!!!!!!!!

    Thank you so much !!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to deal with blank cells to get unique distinct alphabetically sorted list?
    By keyantkarthi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-28-2015, 09:13 AM
  2. [SOLVED] Excel - Count distinct occurences and copy unique values
    By random0munky in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-26-2015, 10:41 PM
  3. Replies: 8
    Last Post: 11-05-2014, 10:27 PM
  4. [SOLVED] Copy Unique Distinct Values
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2013, 10:33 AM
  5. [SOLVED] Unique distinct alphabetically sorted list
    By atlant15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2013, 10:03 AM
  6. Replies: 1
    Last Post: 03-02-2011, 04:14 PM
  7. Copy & Paste Unique Cells Values
    By Michael168 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2005, 07:14 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