excel - How to transpose column of values to row, separated by semi-colon? - Stack Overflow

admin管理员组

文章数量:1291214

I have a csv export of product information, which needs to be uploaded in certain format.

A basic example look like:

So I need to use the Title, across multiple rows to find each Attribute_Value and transpose them into 1 cell, so the desired outcome separated the colors by comma/semi-colon:

Basically my question is how to use a reference value, say using Vlookup (Title in this case) to find row-separated matches so I can combine them into one cell separated by comma. I don't think Transpose() is what I need in this case.

Edit: I don't have access to TextJoin() as I think it may help, too bad.

I have a csv export of product information, which needs to be uploaded in certain format.

A basic example look like:

So I need to use the Title, across multiple rows to find each Attribute_Value and transpose them into 1 cell, so the desired outcome separated the colors by comma/semi-colon:

Basically my question is how to use a reference value, say using Vlookup (Title in this case) to find row-separated matches so I can combine them into one cell separated by comma. I don't think Transpose() is what I need in this case.

Edit: I don't have access to TextJoin() as I think it may help, too bad.

• excel
• vba

Share Improve this question Follow edited Feb 13 at 19:27 HelloWorld asked Feb 13 at 19:03 HelloWorldHelloWorld 11111 silver badge88 bronze badges 1

• I agree with @michal that Power Query is the simplest and fastes way to do this. In VBA you could use a Dictionary object to collect the values. Depending on how complex your real data is, you might want to use a Class object as well (if, for example, you had a number of different attributes for the same Title, each attribute could be a member of the class, and each of those class members could include a collection of the values). It would take quite a bit longer to write, but it is certainly doable. – Ron Rosenfeld Commented Feb 14 at 2:44

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

I would suggest using a different tool as VBA is not really well suited for this task. One was of doing it would be to employ Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    // Fill down the Title and Attribute columns
    FilledDown = Table.FillDown(Source, {"Title", "Attribute"}),

    // Group by Title and Attribute, and concatenate the Value column
    Grouped = Table.Group(FilledDown, {"Title", "Attribute"}, {
        {"Value", each Text.Combine([Value], "; "), type text}
    })
in
    Grouped

Alternatively, you could use Python (adjusting for different inputs and outputs of course, this is just an example):

import pandas as pd

    # Sample data
    data = {
        'Title': ['Red', None, 'Blue', None, None],
        'Attribute': ['Color', None, 'Color', None, None],
        'Value': ['Light', 'Dark', 'Light', 'Medium', 'Dark']
    }
    
    # Create DataFrame
    df = pd.DataFrame(data)
    
    # Fill down the Title and Attribute columns
    df['Title'] = df['Title'].ffill()
    df['Attribute'] = df['Attribute'].ffill()
    
    # Group by Title and Attribute, and concatenate the Value column
    result = df.groupby(['Title', 'Attribute'])['Value'].apply(lambda x: '; '.join(x)).reset_index()
    
    # Display the result
    print(result)

This is my UDF from this answer with the necessary mods.
Place it in a Standard Module.
The input parameter is the range to process.
=stacker(A2:B9)
It returns an array so with non-spill excels select the target range as the size of the source and use CSE (CTRL+SHIFT+ENTER).
Between two result lines there are empty rows left.

Function stacker(rng As Range) As Variant

jnd = rng(1, 2)
lin = 1
ReDim ret(1 To rng.Rows.count, 1 To 2)
ret(1, 1) = rng(1, 1)
For i = 2 To rng.Rows.count
    If rng(i, 1) = "" Then
        jnd = jnd & "; " & rng(i, 2)
        ret(i, 1) = ""
        ret(i, 2) = ""
    Else
        ret(i, 1) = rng(i, 1)
        ret(lin, 2) = jnd
        lin = i
        jnd = rng(i, 2)
    End If
    ret(lin, 2) = jnd
Next i
stacker = ret
End Function

Option Explicit

Sub CreateCSV()

    Const COL_SINGLE = 3 ' column to concat
    Const SEP = ";"
    
    Dim wbOut As Workbook, wsIn As Worksheet
    Dim rngIn As Range, rngOut As Range
    Dim col As Object, s As String, csvfile As String
    Dim lastrow As Long, lastcol As Long, r As Long, n As Long
    Dim t0 As Single: t0 = Timer
    
    Set col = New Collection
    Set wsIn = ThisWorkbook.Sheets("Sheet1")
    
    ' input
    With wsIn
        lastrow = .Cells(.Rows.Count, COL_SINGLE).End(xlUp).Row
        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        ' scan up
        For r = lastrow To 2 Step -1
            ' concat values
            s = .Cells(r, COL_SINGLE) & SEP & s
            ' add row number and string to collection if col A has value
            If Len(.Cells(r, 1)) > 0 Then
                n = n + 1
                s = Left(s, Len(s) - 1) ' remove trailing ;
                col.Add Array(r, s), CStr(n)
                s = ""
            End If
        Next
    End With
    
    ' output
    Set wbOut = Workbooks.Add(1)
    With wbOut.Sheets("Sheet1")
        .Cells(1, 1).Resize(, lastcol) = wsIn.Cells(1, 1).Resize(, lastcol).Value
        For n = col.Count To 1 Step -1
            Set rngIn = wsIn.Cells(col(n)(0), 1).Resize(, lastcol)
            Set rngOut = .Cells(n + 1, 1).Resize(, lastcol)
            rngOut = rngIn.Value
            ' over write with multiple values
            .Cells(n + 1, COL_SINGLE) = col(n)(1)
        Next
    End With
    
    ' save
    csvfile = "Output_" & Format(Now, "yyyymmdd_hhnnss") & ".csv"
    wbOut.SaveAs Filename:=csvfile, FileFormat:=xlCSV
    'wbOut.Close
    
    MsgBox csvfile & " created", vbInformation, Format(Timer - t0, "0.0 secs")
   
End Sub

本文标签： excelHow to transpose column of values to rowseparated by semicolonStack Overflow