[Solved] Excel VBA – Concatenate Rows with duplicate KEY values [closed]

Question

This is the method I described in my comment above:

I would probably look at iteration over the rows, building a delimited string (comma or tab delimited) to represent each “row” in the formatted output, based on the ID, and then write that out to a TXT file that could easily be read by SPSS

Here’s the code. It is slightly more than the 30 lines I estimated 🙂

Sub FormatDataFileForSPSS()
Dim rng As Range            'the range representing the entire set of data to be formatted
Dim r As Range              'row iterator for the data table
Dim key As Variant          'id number
Dim rowData As String       'concatenated row data
Dim outputPath As String    'the place to put the output file
Dim outputFile As String    'the file name

'--- REQUIRES REFERENCE TO MICROSOFT SCRIPTING RUNTIME ---
Dim dict As Scripting.Dictionary 'a dictionary that we will use to concat each row by ID
Dim fso As Scripting.FileSystemObject 'used to write the output file

'Begin procedure here...

'Allow the user to select a range of data to format
' do NOT select the "header" row!
Set rng = Application.InputBox("Select the data to be formatted", "Select Data", Type:=8)

'Create the dictionary:
Set dict = CreateObject("Scripting.Dictionary")

'get the destination for the output file:
outputPath = CreateObject("Wscript.Shell").SpecialFolders("Desktop") 'Or modify to use a different filepath
outputFile = outputPath & "\my output.txt" 'Modify as needed

'Iterate the data table:
For Each r In rng.Rows
    'get the key value
    key = r.Cells(1, 1).Value
    'Concatenate the row data to a string
    rowData = r.Cells(1, 2) & vbTab & r.Cells(1, 3) & vbTab & r.Cells(1, 4) & vbTab & r.Cells(1, 5)
    'Check if this KEY value already exists
    If Not dict.Exists(key) Then
        'if not, then add it to the dictionary
        dict.Add key, rowData
    Else:
        'Append to the existing key's value:
        dict(key) = dict(key) & vbTab & rowData
    End If
Next

'Create our FileSystemObject to write the text file:
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.CreateTextFile(Filename:=outputFile, overwrite:=True, unicode:=False)
    For Each key In dict.Keys
        .WriteLine dict(key)
    Next
    .Close
End With

End Sub

Output as tab-delimited, without header row (since the headers in your example aren’t unique to begin with). I am fairly certain that you can specify in SPSS to import the data without header row, and it will assign default variable names that you can later modify if needed.

Here is the data when viewed in SPSS (follow prompts to open delimited text file)

Or you can open the TXT delmited file in Excel and follow a few prompts, specifying it as Tab-delimited, then you could add the header information in the Excel file:

Accepted Answer