Transpose 2 columns into multiple rows

[merig00]

I've read several posts on how to do it and still can't figure it out so hopefully you'll be able to help me.

I have 2 columns and 1300 lines.

Column A has Product Type that repeats and Column B has Measurements.

Basket	Depth
Basket	Length
Basket	Width
Activewear	Waist
Activewear	Hip
Activewear	Inside_Leg

<tbody>
</tbody>

What I need to have is

Basket	Depth	Length	Width
Activewear	Waist	Hip	Inside_Leg

<tbody>
</tbody>

I have a total of 140 unique product types spread out over 1300 rows.

Thank you.

 

[Rick Rothstein]

I've read several posts on how to do it and still can't figure it out so hopefully you'll be able to help me.

I have 2 columns and 1300 lines.

Column A has Product Type that repeats and Column B has Measurements.

Basket	Depth
Basket	Length
Basket	Width
Activewear	Waist
Activewear	Hip
Activewear	Inside_Leg

<TBODY>
</TBODY>

What I need to have is

Basket	Depth	Length	Width
Activewear	Waist	Hip	Inside_Leg

<TBODY>
</TBODY>

I have a total of 140 unique product types spread out over 1300 rows.

Thank you.

Will each product in your raw data always consist of three rows of data as your two examples above did, row could the number of rows of data for each product vary?

 

[merig00]

They all vary to great length depending on product type. So it could be 3 or could be 10. Thank you for pointing it out. I didn't think about it when coming up with an example.

 

[markmzz]

Maybe this:

Layout:

Product Type	Measurements		Product Type	Measurements
Basket	Depth		Basket	Depth	Length	Width
Basket	Length		Activewear	Waist	Hip	Inside_Leg
Basket	Width
Activewear	Waist
Activewear	Hip
Activewear	Inside_Leg
************	**************	****	************	*************	*******	**********	**********

<tbody>
</tbody>

Formulas:

Normal formula (use Enter)

D2-> =IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$7),),0)),"")


Array formula (use Ctrl+Shift+Enter and not only Enter)

E2-> =IFERROR(INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$D2,ROW($B$2:$B$7)-ROW($B$2)+1),COLUMNS($D2:D2))),"")

Markmzz

 

[merig00]

Hi guys,

Thank you so much for all your help! Sorry, I couldn't respond earlier - been busy with month end.

My friend also found a macro that works. Here it is, just in case someone else stumbles upon this topic with the same problem:

Sub Transpose2columns()
    Dim A, i As Long, e, x, y
    With Range("a1").CurrentRegion
        A = .Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 1 To UBound(A, 1)
                If Not .exists(A(i, 1)) Then
                    Set .Item(A(i, 1)) = _
                    CreateObject("Scripting.Dictionary")
                End If
                .Item(A(i, 1))(A(i, 2)) = Empty
            Next
            x = .keys: y = .items
        End With
        With .Offset(, .Columns.Count + 2).Cells(1)
            .CurrentRegion.ClearContents
            For i = 0 To UBound(x)
                .Offset(i).Value = x(i)
                .Offset(i, 1).Resize(, y(i).Count).Value = y(i).keys
            Next
        End With
    End With
End Sub