Move all text values with the same ID field to separate columns-CodePudding

I would like to move all the comments (Column B3:B14) to be new columns against each unique ID (Column A3:A14).

The Desired Format shows the layout that I would like to get to.

Hopefully that makes sense.

CodePudding user response：

This will do what you want:

Option Explicit

Sub TransposeComments()

    Dim inSR%, inTR%, inTC%, rgSource As Range, rgTarget As Range
    
    Set rgSource = Range("A3")  'Change this if the 1st ID in the source table is moved
    Set rgTarget = Range("D3")  'Change this to start populating at another start point
    inTR = -1
    
    Do
        If rgSource.Offset(inSR) <> rgSource.Offset(inSR - 1) Then
            inTR = inTR   1: inTC = 2
            rgTarget.Offset(inTR) = rgSource.Offset(inSR)
            rgTarget.Offset(inTR, 1) = rgSource.Offset(inSR, 1)
            
        Else
            rgTarget.Offset(inTR, inTC) = rgSource.Offset(inSR, 1)
            inTC = inTC   1
        End If
        
        inSR = inSR   1
    
    ''' End on 1st empty ID (assumes ID's in source data are contiguous and nothing is below them)
    Loop Until rgSource.Offset(inSR) = ""

End Sub

I've assumed you know how to implement and call/run the vb. If not, let me know and I try and help with that. :)

CodePudding user response：

You can obtain your desired output using Power Query, available in Windows Excel 2010 and Office 365 Excel

Select some cell in your original table
Data => Get&Transform => From Table/Range
When the PQ UI opens, navigate to Home => Advanced Editor
Make note of the Table Name in Line 2 of the code.
Replace the existing code with the M-Code below
Change the table name in line 2 of the pasted code to your "real" table name
Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let

//read in the data
//change table name in next line to actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Comments", type text}}),

//group by ID and concatenate the comments with a character not used in the comments
//I used a semicolon, but that could be changed
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"Comment", each Text.Combine([Comments],";")},

//also generate Count of the number of comments in each ID group
//as the Maximum will be the count of the number of columns to eventually create
        {"numCols", each Table.RowCount(_)}    
        }),

//calculate how many columns to create and delete that column
    maxCols = List.Max(#"Grouped Rows"[numCols]),
    remCount = Table.RemoveColumns(#"Grouped Rows","numCols"),

//Split into new columns
    #"Split Column by Delimiter" = Table.SplitColumn(remCount, "Comment", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),maxCols)
in
    #"Split Column by Delimiter"