Print rows as cols without repeats

S

silversmith

New Member
Joined
Jun 23, 2019
Messages
5
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​
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
using PowerQuery and PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td=bgcolor:#5B9BD5]Column6[/td][td=bgcolor:#5B9BD5]Column7[/td][td][/td][td=bgcolor:#DDEBF7]Column1[/td][td=bgcolor:#DDEBF7]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]name1[/td][td=bgcolor:#DDEBF7]xxx[/td][td=bgcolor:#DDEBF7]qqq[/td][td=bgcolor:#DDEBF7]www[/td][td=bgcolor:#DDEBF7]eee[/td][td=bgcolor:#DDEBF7]rrr[/td][td=bgcolor:#DDEBF7]ttt[/td][td][/td][td]name1[/td][td]eee[/td][/tr]

[tr=bgcolor:#FFFFFF][td]name2[/td][td]yyyy[/td][td]aaa[/td][td]bbb [/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]qqq[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]name3[/td][td=bgcolor:#DDEBF7]zzz[/td][td=bgcolor:#DDEBF7]aaa[/td][td=bgcolor:#DDEBF7]fff[/td][td=bgcolor:#DDEBF7]eee[/td][td=bgcolor:#DDEBF7]ggg[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td]rrr[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]name2[/td][td]aaa[/td][/tr]

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

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

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]name3[/td][td]aaa[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]zzz[/td][/tr]
[/table]

Code: 
[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
 
Upvote 0
Same idea here. Only instead of using a pivot table, I just used conditional formatting to hide the duplicate names.

PQ
Code: 
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.

Code: 
=COUNTIF($I$2:I2,I2)>1
 
Upvote 0
And just for fun, here's a VBA solution as well.

Code: 
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
 
Upvote 0
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:

[TABLE="width: 640"]
<tbody>[TR]
[TD]column1[/TD]
[TD]column2[/TD]
[TD]column3[/TD]
[TD]column4[/TD]
[TD]column5[/TD]
[TD]column6[/TD]
[TD]column7[/TD]
[TD][/TD]
[TD="colspan: 2"]NAME@VALUE[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD]xxx[/TD]
[TD]qqq[/TD]
[TD]www[/TD]
[TD]eee[/TD]
[TD]rrr[/TD]
[TD]ttt[/TD]
[TD][/TD]
[TD="colspan: 2"]column1@column2[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD]yyyy[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]@column3[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD]zzz[/TD]
[TD]aaa[/TD]
[TD]fff[/TD]
[TD]eee[/TD]
[TD]ggg[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]@column4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]@column5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]@column6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]@column7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]name1@xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@qqq[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@www[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@eee[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@rrr[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@ttt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]name2@yyyy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@aaa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@bbb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]name3@zzz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@aaa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@fff[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@eee[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]@ggg

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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
 
Upvote 0
Try this.

Code: 
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
 
Upvote 0
This is what I get this time from the code:

[TABLE="width: 636"]
<colgroup><col><col><col span="8"></colgroup><tbody>[TR]
[TD]name1[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]qqq[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name1[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]www[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]qqq[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]eee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]www[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]fff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]eee[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]ttt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]fff[/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD]aaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]ttt[/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD]bbb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yyy[/TD]
[TD]aaa[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]aaa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yyy[/TD]
[TD]bbb[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]fff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zzz[/TD]
[TD]aaa[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]jjj[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zzz[/TD]
[TD]fff[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]kkk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zzz[/TD]
[TD]jjj[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zzz[/TD]
[TD]kkk[/TD]
[/TR]
</tbody>[/TABLE]

Maybe we are getting closer? Many thanks.
Peter
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Progress in Status Bar For Macro That Loops Through All Excel Files in a Folder
2
Replies
10
Views
5K
AlphaFrog
AlphaFrog
K
  • Question Question
remove end commas on dates
Replies
3
Views
1K
Joe4
Joe4
A
  • Question Question
Formula or VBA to match a Sheet name to a particular cell and if it matches do a SUMIF on the worksheet than matches it.
Replies
0
Views
736
allygater
A
B
  • Question Question
Indented Headings in single column - need to extract to multiple columns
Replies
1
Views
1K
brett24
B
I
  • Question Question
Sorting columns help
Replies
1
Views
557
jasonb75
J

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top