Create multiple rows for multiple data points in the same attribute

[agnes61]

How can I create multiple rows when there are multiple data points for the same attribute? Thanks!!!

For example, if there are 3 investors in company ABC, then there will be 3 rows
[FONT=&quot]
-------------------------------------------------------------------------

[/FONT]

From:
Company	Investor_1	Investor_2	Investor_3
ABC	X	Y	Z
DEF	U	X
……
To:
Deal_ID	Company	Investors
ABC_X	ABC	X
ABC_Y	ABC	Y
ABC_Z	ABC	Z
DEF_U	DEF	U
DEF_X	DEF	X
……

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[VBA Geek]

you need a bit of vba to do this

add this code and run it

Sub trnsp()
Dim iArr As Variant, iRow As Long, iCol As Long, resVar As Variant, iCounter As Long, selectedRng As Excel.Range

On Error Resume Next
Set selectedRng = Application.InputBox("Select the region of your data", , , , , , , 8)
On Error GoTo 0

If selectedRng Is Nothing Then Exit Sub

iArr = selectedRng.Value

ReDim resVar(0 To 2, 0 To 500)
resVar(0, 0) = "Deal_ID"
resVar(1, 0) = "Company"
resVar(2, 0) = "Investors"

For iRow = LBound(iArr, 1) + 1 To UBound(iArr, 1)
    For iCol = LBound(iArr, 2) + 1 To UBound(iArr, 2)
        If Not iArr(iRow, iCol) = vbNullString Then
            iCounter = iCounter + 1
            If iCounter > UBound(resVar, 2) Then ReDim Preserve resVar(0 To 2, 0 To UBound(resVar, 2) + 500)
            resVar(0, iCounter) = iArr(iRow, 1) & "_" & iArr(iRow, iCol)
            resVar(1, iCounter) = iArr(iRow, 1)
            resVar(2, iCounter) = iArr(iRow, iCol)
        End If
    Next iCol
Next iRow
If iCounter < UBound(resVar, 2) Then ReDim Preserve resVar(0 To 2, 0 To iCounter)
Application.InputBox("Select where to export your results", , , , , , , 8).Cells(1, 1).Resize(UBound(resVar, 2) + 1, UBound(resVar, 1) + 1).Value = Application.Transpose(resVar)
End Sub