+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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,767

    Re: Consolidate and Concatenate by Creating Columns

    Try this

    Please Login or Register  to view this content.
    Martin

    If my solution has saved you time and/or money, please consider donating to Cancer Research UK or your local equivalent.

    https://www.cancerresearchuk.org/

  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,767

    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
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    176

    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
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    176

    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
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    176

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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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