Simple Transpose problem

[Maajid]

Hello everyone,

I have a very simple transpose problem that I can't quite figure out. The data I have is currently in this format:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Response[/TD]
[/TR]
[TR]
[TD]Respondent 1[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Respondent 2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]


I want to transpose it so it is in the following format:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Response 1[/TD]
[TD]Response 2[/TD]
[TD]Response 4[/TD]
[TD]Response 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Respondent 1[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Respondent 2[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I know I can easily do this copying and pasting but the number of respondents is almost 2,000 and it wouldn't be possible to individually paste each respondent's responses and applying a transpose. If anyone has any ideas as to how to do this quickly, I'd be grateful.

 

[sandy666]

you can try PowerQuery (Get&Transform)
(simple version)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Response[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Response.1[/td][td=bgcolor:#70AD47]Response.2[/td][td=bgcolor:#70AD47]Response.3[/td][td=bgcolor:#70AD47]Response.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Respondent 1[/td][td=bgcolor:#DDEBF7]X[/td][td][/td][td=bgcolor:#E2EFDA]Respondent 1[/td][td=bgcolor:#E2EFDA]X[/td][td=bgcolor:#E2EFDA]Y[/td][td=bgcolor:#E2EFDA]Z[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Y[/td][td][/td][td]Respondent 2[/td][td]A[/td][td]B[/td][td]C[/td][td]D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Z[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Respondent 2[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]B[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]C[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]D[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Name"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Response", each Table.Column([Count],"Response")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Response", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Response", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Response.1", "Response.2", "Response.3", "Response.4"})
in
    #"Split Column by Delimiter"[/SIZE]

 

[Maajid]

Thank you for the swift reply, I really appreciate it.

I am using Excel for Mac 2019 though and from what I can see, the PowerQuery add-in is not available for the Mac version of Excel. Is there an alternative to this?

Thanks again.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H
1	Name	Response		Name	Response 1	Response 2	Response 3	Response 4
2	Respondent 1	X		Respondent 1	X	Y	Z
3		Y		Respondent 2	A	B	C	D
4		Z		Respondent 3	G	H
5	Respondent 2	A
6		B
7		C
8		D
9	Respondent 3	G
10		H
81

Cell Formulas
Range	Formula
E2	=IF((COLUMN(E$1)-COLUMN($D$1))>SUMPRODUCT(--(LOOKUP(ROW($A$2:$A$2000),ROW($A$2:$A$2000)/($A$2:$A$2000>0),$A$2:$A$2000)=$D2)),"",OFFSET($A$1,MATCH($D2,$A:$A,0)+COLUMN(E$1)-COLUMN($F$1),1))

 

[footoo]

Another way :

Sub RwsToCols()
Dim r%: r = 2
Dim c: c = 3
Application.ScreenUpdating = False
Do While Not IsEmpty(Cells(r, 2))
    If IsEmpty(Cells(r, 1)) Then
        Cells(r - 1, c) = Cells(r, 2)
        Rows(r).Delete Shift:=xlUp
        c = c + 1
    Else
        c = 3
        r = r + 1
    End If
Loop
End Sub

 

[sandy666]

I am using Excel for Mac 2019

You didn't say that in the first post

Have a nice day

 

[DanteAmor]

I'm not sure if it works on mac, but put your data on sheet1 and create a sheet called "sheet2"

Run this macro:

Sub Macro2()
    Dim sh1 As Worksheet, sh2 As Worksheet, wArea As Range, rArea As Range
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Cells.ClearContents
    For Each wArea In sh1.Range("A2", sh1.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).Areas
        Set rArea = wArea.Resize(wArea.Rows.Count + 1).Offset(-1, 1)
        sh2.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = wArea.Offset(-1, 0).Value
        sh2.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(1, rArea.Count) = WorksheetFunction.Transpose(rArea.Value)
    Next
    MsgBox "End"
End Sub