Print rows as cols without repeats

[silversmith]

Print rows as cols without repeats

<ins id="aswift_2_expand" data-ad-slot="9830241510" style="text-decoration-line: none; display: inline-table; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;"><ins id="aswift_2_anchor" style="text-decoration-line: none; display: block; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;">******** width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_2" name="aswift_2" style="left: 0px; position: absolute; top: 0px; border-width: 0px; border-style: initial; width: 300px; height: 250px;">*********></ins></ins>
I have a Excel sheet in which all of a column 1 names may have one to 10 other names in its row.
I want to print out a sheet (Word or Excel) with column 1 name followed by a single column with each of the names in its row .

For example data:

name1 xxx qqq www eee rrr ttt
name2 yyyy aaa bbb
name3 zzz aaa fff eee ggg

desired output:

name1 xxx
------- qqq
------- www
------- eee
------- rrr
------- ttt

name2 yyyy
-------- aaa
-------- bbb

name3 zzz
------- aaa
------- fff
------- eee
------- ggg

(note: dashes are supposed to be empty cell, but this forum editor won't allow spaces)
How do I do this? The number names in a row varies, so regular template won't work. Many Thanks!
Peter​

 

[sandy666]

using PowerQuery and PivotTable

Column1	Column2	Column3	Column4	Column5	Column6	Column7		Column1	Value
name1	xxx	qqq	www	eee	rrr	ttt		name1	eee
name2	yyyy	aaa	bbb						qqq
name3	zzz	aaa	fff	eee	ggg				rrr
									ttt
									www
									xxx
								name2	aaa
									bbb
									yyyy
								name3	aaa
									eee
									fff
									ggg
									zzz

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value")
in
    Unpivot[/SIZE]

then use PivotTable from external source

 

[lrobbo314]

Same idea here. Only instead of using a pivot table, I just used conditional formatting to hide the duplicate names.

PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Column1"},"Attribute", "Value"),
    Remove = Table.RemoveColumns(Unpivot,{"Attribute"}),
    Rename = Table.RenameColumns(Remove,{{"Column1", "Name"}})
in
    Rename

Conditional Format formula. Select 'Name' column, in this example, from I2:I15, and paste this formula and set font color to white.

=COUNTIF($I$2:I2,I2)>1

 

[lrobbo314]

And just for fun, here's a VBA solution as well.

Sub xPose()
Application.ScreenUpdating = False
Dim AR() As Variant: AR = Range("A1").CurrentRegion
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")

AL.Add ("NAME@VALUE")

For i = LBound(AR) To UBound(AR)
    For j = LBound(AR) + 1 To UBound(AR, 2)
        If AR(i, j) <> vbNullString Then
            If j = 2 Then
                AL.Add AR(i, 1) & "@" & AR(i, j)
            Else
                 AL.Add "@" & AR(i, j)
            End If
        End If
    Next j
Next i

With Range("I1").Resize(AL.Count, 1)
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, OtherChar:="@", FieldInfo:=Array(Array(1, 1), Array(2, 1))
End With

Application.ScreenUpdating = True
End Sub

 

[silversmith]

Thank you so much for your responses.

For the non VBA methods,(Sandy666, Irobbo314) I really don't know where to put this code to implement. Please guide me.

For the VBA (Irobbo314) when I run this module, I get:

column1	column2	column3	column4	column5	column6	column7		NAME@VALUE
name1	xxx	qqq	www	eee	rrr	ttt		column1@column2
name2	yyyy	aaa	bbb					@column3
name3	zzz	aaa	fff	eee	ggg			@column4
								@column5
								@column6
								@column7
								name1@xxx
								@qqq
								@www
								@eee
								@rrr
								@ttt
								name2@yyyy
								@aaa
								@bbb
								name3@zzz
								@aaa
								@fff
								@eee
								@ggg

<tbody>
</tbody>

Obviously my inexperience is showing. Please hand hold me to implement both methods, so I can learn and get the desired output.
Many, Many, Many thanks. Peter

 

[lrobbo314]

Try this.

Sub xPose()
Application.ScreenUpdating = False
Dim AR() As Variant: AR = Range("A1").CurrentRegion
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")


AL.Add ("NAME@VALUE")


For i = LBound(AR) To UBound(AR)
    For j = LBound(AR) + 1 To UBound(AR, 2)
        If AR(i, j) <> vbNullString Then
            If j = 2 Then
                AL.Add AR(i, 1) & "@" & AR(i, j)
            Else
                 AL.Add "@" & AR(i, j)
            End If
        End If
    Next j
Next i


With Range("I1").Resize(AL.Count, 1)
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="@", Tab:=False, Semicolon:=False, Space:=False, Comma:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1))
End With


Application.ScreenUpdating = True
End Sub

 

[sandy666]

for non-vba see movie: PowerQuery & PivotTable

 

[silversmith]

This is what I get this time from the code:

name1	Value							NAME	VALUE
xxx	qqq							name1	Value
xxx	www							xxx	qqq
xxx	eee							xxx	www
xxx	fff							xxx	eee
xxx	ttt							xxx	fff
yyy	aaa							xxx	ttt
yyy	bbb							yyy	aaa
zzz	aaa							yyy	bbb
zzz	fff							zzz	aaa
zzz	jjj							zzz	fff
zzz	kkk							zzz	jjj
								zzz	kkk

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

Maybe we are getting closer? Many thanks.
Peter

 

[silversmith]

for non-vba see movie: PowerQuery & PivotTable

THIS MOVIE IS GREAT!!!!
Thank you.
Peter

 

[sandy666]

THIS MOVIE IS GREAT!!!!
Thank you.
Peter

You are most welcome

Have a nice day