+ Reply to Thread
Results 1 to 24 of 24

Consolidate and Concatenate by Creating Columns

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

    Consolidate and Concatenate by Creating Columns

    I need to consolidate rows into a single row, adding columns as necessary.

    Problem:
    ID relation name date
    68540 Your loved one Agnes A. Schmidt 9-Aug
    68540 Your cousin Emry 25-Sep
    68540 Your friend Charles 4-Oct

    Desired Solution:
    ID relation name date relation2 name2 date2 relation3 name3 date3
    68540 Your loved one Agnes A. Schmidt 9-Aug Your cousin Emry 25-Sep Your friend Charles 4-Oct

    There are over 35,000 rows that I need to consolidate.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Consolidate and Concatenate by Creating Columns

    Try this

    Please Login or Register  to view this content.
    Martin

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

    Re: Consolidate and Concatenate by Creating Columns

    I don't know how to create macros from code

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

    Re: Consolidate and Concatenate by Creating Columns

    It says object required. When I click Debug, it points to Sheet2.Cells(TargetRow, 1_ = Cells(N, 1)

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Consolidate and Concatenate by Creating Columns

    Open up the VBA editor by hitting ALT F11

    Insert a new module by hitting Insert - Module

    Paste the macro into the empty sheet

    Hit ALT F11 to get back to the worksheet.

    Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010/2013.

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

    Re: Consolidate and Concatenate by Creating Columns

    That's what I did, but it gave me the Run-time error '424': Object Required

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

    Re: Consolidate and Concatenate by Creating Columns

    Please see the attachment for the exact columns I will be working with. I don't know if it makes a difference in the macro, but these are the columns I will be working with.
    Attached Files Attached Files

  8. #8
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Consolidate and Concatenate by Creating Columns

    Hi, to all!

    Check this example. I work with your first file. This is the code:
    PHP Code: 
    Sub ReOrdData()
        
    Dim arc&, d&, s&, q&, f$, m&, i&, j&
        
        
    Range("A1").CurrentRegion.Value
        s 
    Range("A" Rows.Count).End(xlUp).Row
        
        f 
    "frequency(A2:A" ",A2:A" ")"
        
    Evaluate("sum(n(" ">0))"): Evaluate("max(" ")")
        
    ReDim r(1 To q1 To 1 m)
        
        For 
    LBound(aTo UBound(a)
            If 
    a(i1) <> a(11Then
                d 
    d1
                r
    (d1) = a(i1)
            
    End If
            For 
    2 To 4
                c 
    cr(dc) = a(ij)
            
    Next j
        Next i
        
        With Range
    ("G1")
            .
    CurrentRegion.Clear
            
    .Value "ID"
            
    With .Resize(, 4)
                .
    Value = [{"ID""relation 1""name 1""date 1"}]
                .
    Offset(, 1).Resize(, 3).AutoFill .Offset(, 1).Resize(, m)
            
    End With
            With 
    .Resize(, m)
                .
    Font.Bold True
                
    .Interior.Color rgbLightGray
            End With
            
    .Offset(1).Resize(qm) = r
            
    .CurrentRegion.Columns.AutoFit
        End With
        
        Erase a
    r
        
    End Sub 
    For work, click on arrow. Blessings!
    Attached Files Attached Files

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

    Re: Consolidate and Concatenate by Creating Columns

    @johnmpl thank you! Could you manipulate that code for the second file I uploaded?

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

    Re: Consolidate and Concatenate by Creating Columns

    @johnmpl the .Interior.Color line messed it up. Once I removed it, I was able to run the macro

  11. #11
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Consolidate and Concatenate by Creating Columns

    Hi, again!

    The columns have more data. Upload a file with the resultant data like your new attach. Blessings!

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

    Re: Consolidate and Concatenate by Creating Columns

    Sorry, I don't understand what you need. I've attached the actual columns excel file.
    Attached Files Attached Files

  13. #13
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Consolidate and Concatenate by Creating Columns

    Quote Originally Posted by andytaylordesigns View Post
    Sorry, I don't understand what you need. I've attached the actual columns excel file.
    What I wanted to say to you is: What exactly are the columns you need as a result and in what order do you need them according to the new file you uploaded?

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

    Re: Consolidate and Concatenate by Creating Columns

    Oh I get it. Ok. the repetitive columns like address and salutation, Id and first date column don't need to have extra columns. The only difference between each record is the relative name, relation, and last date column. It's those three last columns that need to be added as extra columns to make one record.

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

    Re: Consolidate and Concatenate by Creating Columns

    So the column names will be: Date Street 1 Name ID Street 2 city state zip salutation relation relation name dod relation2 relation name2 dod2 relation3 relation name3 dod3 (Etc.)

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    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

  17. #17
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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

  18. #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

  19. #19
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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...

  20. #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.

  21. #21
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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?

  22. #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.

  23. #23
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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.

  24. #24
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    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

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 07-28-2016, 01:47 AM
  2. Creating macro to consolidate data from multiple worksheets
    By MzDingle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-25-2015, 03:52 PM
  3. [SOLVED] Help with data consolidation or creating formulas to consolidate
    By jojo101 in forum Excel General
    Replies: 7
    Last Post: 06-21-2015, 07:34 AM
  4. [SOLVED] Creating a formula using CONCATENATE or INDIRECT
    By bronsonb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-04-2014, 12:12 PM
  5. Creating a unique ID using the Concatenate function
    By mdovey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2014, 01:49 PM
  6. Consolidate many columns into three
    By Wilco84 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-16-2013, 07:58 AM
  7. Consolidate some columns into a row
    By martinez_pedro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-27-2011, 06:09 PM

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