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:

Name	Response
Respondent 1	X
	Y
	Z
Respondent 2	A
	B
	C
	D

<tbody>
</tbody>

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

Name	Response 1	Response 2	Response 4	Response 5
Respondent 1	X	Y	Z
Respondent 2	A	B	C	D

<tbody>
</tbody>

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)

Name	Response		Name	Response.1	Response.2	Response.3	Response.4
Respondent 1	X		Respondent 1	X	Y	Z
	Y		Respondent 2	A	B	C	D
	Z
Respondent 2	A
	B
	C
	D

[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