0

Column A contains the labels or outcome value, Columns B-N contain varying lengths of comma separated values, but range for each column is the same (i.e., 1-64). The goal is to covert to a new table with Column A representing the value range (1-64) and Columns B-N the labels/outcome from the original table.

Example

A semi-related solution was sought here, but without use of macros.

Community
  • 1
  • 1
  • 1
    did u try anything in vba so far? – Gowtham Shiva Aug 12 '17 at 14:23
  • 1
    Looks a Little bit like "could someone please do it for me?". – sancho.s ReinstateMonicaCellio Aug 12 '17 at 14:38
  • I'll admit, my vba skills are novice level. The original data I'm pulling from is formatted as "1-5", "6-15", etc. So I've been working on converting data to comma delimited, and playing with different options (e.g., comma delimited within cell vs. expanding to columns. I found a semi-solution that involved transposing the data and copy-paste using pivot tables, but all of it seemed very inefficient. I'm more stuck on finding the most efficient solution as this is only one part of the larger puzzle I'm working with. Sorry if it seems like I'm just leeching. –  Aug 12 '17 at 15:01

2 Answers2

1

I will let you to modify this code,

Sub splitThem()
Dim i As Long, j As Long, k As Long, x As Long
x = 1
Sheets.Add.Name = "newsheet"
For j = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(i, j) <> "" Then
    For k = 1 To Len(Cells(i, j)) - Len(Replace(Cells(i, j), ",", "")) + 1
        Sheets("newsheet").Cells(x, j) = Cells(i, 1)
        x = x + 1
    Next k
End If
Next i
x = 1
Next j
End Sub

enter image description here

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
  • Thank you for such a quick response, much appreciated. I made minor adjustments to point things to the sheet with data and its works great for the correct number of "A"s but then only adds one instance of B,C,D,E, and F. –  Aug 12 '17 at 15:27
0

Try this code.

Sub test()
    Dim vDB, vR()
    Dim vSplit, v As Variant
    Dim Ws As Worksheet
    Dim i As Long, n As Long, j As Integer, c As Integer

    vDB = Range("a2").CurrentRegion
    n = UBound(vDB, 1)
    c = UBound(vDB, 2)

    ReDim vR(1 To 64, 1 To c)
    For i = 1 To 64
        vR(i, 1) = i
    Next i


    For i = 2 To n
        For j = 2 To c
            vSplit = Split(vDB(i, j), ",")
                For Each v In vSplit
                    vR(v, j) = vDB(i, 1)
                Next v
        Next j
    Next i
    Set Ws = Sheets.Add '<~~ replace your sheet : Sheets(2)
    With Ws
        For i = 1 To c
            .Range("b1")(1, i) = "COND" & i
        Next i
        .Range("a2").Resize(64, c) = vR
    End With
End Sub
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14