+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 24 of 24

Consolidate and Concatenate by Creating Columns

  1. #16
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,979

    Re: Consolidate and Concatenate by Creating Columns

    If I understand correctly the sample source having one record of 31 relatives/friends would require 93 columns?

    Please see the attached.

    In column D IDs are copied/pasted and the applied 'Remove duplicates'.

    In E2 filled down until IDs are exhausted and across to column CY this formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  2. #17
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    Re: Consolidate and Concatenate by Creating Columns

    Hi again, andytaylordesigns!

    Check new code:
    PHP Code: 
    Sub ReOrdData()
        
    Dim arc&, d&, s&, q&, f$, m&, i&, j&
         
        
    Range("A1").CurrentRegion.Value
        s 
    Range("D" Rows.Count).End(xlUp).Row
         
        f 
    "frequency(D2:D" ",D2:D" ")"
        
    Evaluate("sum(n(" ">0))"): Evaluate("max(" ")")
        
    ReDim r(1 To q1 To 7 m)
         
        For 
    LBound(aTo UBound(a)
            If 
    a(i4) <> a(14Then
                d 
    d7
                
    For 1 To 7
                    r
    (dj) = a(ij)
                
    Next j
            End 
    If
            For 
    8 To 10
                c 
    cr(dc) = a(ij)
            
    Next j
        Next i
         
        With Range
    ("M1")
            .
    CurrentRegion.Clear
            Range
    ("A1:G1").Copy Range("M1")
            
    With .Offset(, 7)
                .
    Resize(, 3) = [{"relation 1""relation name 1""dod 1"}]
                .
    Resize(, 3).AutoFill .Resize(, m)
            
    End With
            With 
    .Resize(, m)
                .
    Font.Bold True
                
    .Interior.Color RGB(180180180)
            
    End With
            
    .Offset(1).Resize(qm) = r
            
    .CurrentRegion.Columns.AutoFit
        End With
         
        Erase a
    r
    End Sub 
    Check file. Blessings!
    Attached Files Attached Files

  3. #18
    Registered User
    Join Date
    01-02-2018
    Location
    Oregon
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Consolidate and Concatenate by Creating Columns

    @johnmpl Runtime Error 9 Subscript out of range

  4. #19
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    Re: Consolidate and Concatenate by Creating Columns

    Quote Originally Posted by andytaylordesigns View Post
    @johnmpl Runtime Error 9 Subscript out of range
    Which line have errors? I try the code above, just press the arrow, and work all fine...

  5. #20
    Registered User
    Join Date
    01-02-2018
    Location
    Oregon
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Consolidate and Concatenate by Creating Columns

    Quote Originally Posted by johnmpl View Post
    Which line have errors? I try the code above, just press the arrow, and work all fine...
    It works with those few rows, but when I try the script with all 35,000 rows, it gives me that error.

  6. #21
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    Re: Consolidate and Concatenate by Creating Columns

    Quote Originally Posted by andytaylordesigns View Post
    It works with those few rows, but when I try the script with all 35,000 rows, it gives me that error.
    Can you upload to google drive, or one drive, and share the link here?

  7. #22
    Registered User
    Join Date
    01-02-2018
    Location
    Oregon
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Consolidate and Concatenate by Creating Columns

    Quote Originally Posted by johnmpl View Post
    Can you upload to google drive, or one drive, and share the link here?
    I'm sorry I can't, it's proprietary information.

  8. #23
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    Re: Consolidate and Concatenate by Creating Columns

    Quote Originally Posted by andytaylordesigns View Post
    I'm sorry I can't, it's proprietary information.
    Change some sensible data, and send it. It's hard to say what's going on without see your file.

  9. #24
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    Re: Consolidate and Concatenate by Creating Columns

    Try something: Sort your data by ID column (Column D) and run the code again, because this code works when the data is sorted by ID. Blessings!

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

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

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