Single list from multiple columns
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Single list from multiple columns

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a worksheet with multiple columns, anywhere from 2 to 10 or more. These columns are lists of numbers (UPC codes, actually). The columns are of different lengths. Some numbers are common to all columns and some are common to only a few and some are unique to one column. What I need to do is combine the columns into a single list of numbers with no duplications. This will be a grocery store chain authorization list. I do this manually now. I would appreciate any help.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ranger

    The quickest way would be via Excels Advanced Filter found under data>Filter. This means you would need to place all Columns into a single Column then go there and set it for Unique values only.



  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Ranger


    Here is a VBA method. You MUST have Columns "A" and "B" empty. You then just select each ENTIRE Column containing you data by using holding down the Ctrl key

    Sub ExtractUniques()
    Dim i As Integer
    Dim rRange As Range
    'Written By Dave Hawley of http://www.OzGrid.com
    Range("A1") = "Unique List"
    Range("A1").Font.Bold = True
    Set rRange = Selection
    For i = 1 To rRange.Areas.Count
    With rRange.Areas(i).Columns(1)
    Range(.Cells(1, 1), .Cells(65536, 1).End(xlUp)).Copy _
    Destination:=Range("A65536").End(xlUp).Offset(1, 0)
    End With
    Next

    Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Range("B1"), unique:=True
    Columns(1).Delete
    End Sub


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