Names and Address columns to rows?

darrenmccoy

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
37
Office Version
  1. 2019
Hi Guys, Could someone help me work out the script or formula to index these columns and create new rows with the names in different columns?

This data columns containing names address and address turns into address with rows of names ->

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="170"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]NAME[/TD]
[TD]ADDRESS[/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"]ADDRESS[/TD]
[TD="bgcolor: #fff2cc"]Name1[/TD]
[TD="bgcolor: #fff2cc"]Name2[/TD]
[TD="bgcolor: #fff2cc"]Name3[/TD]
[/TR]
[TR]
[TD]john smith[/TD]
[TD]4 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #FFF2CC"]4 CHAMPION ST [/TD]
[TD="bgcolor: #fff2cc"]john smith[/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]don howel[/TD]
[TD="bgcolor: #F3F3F3"]5 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #FFF2CC"]5 CHAMPION ST [/TD]
[TD="bgcolor: #fff2cc"]don howel[/TD]
[TD="bgcolor: #fff2cc"]james jonson[/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]james jonson[/TD]
[TD]5 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #FFF2CC"]6 CHAMPION ST [/TD]
[TD="bgcolor: #fff2cc"]wayne not[/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]wayne not[/TD]
[TD="bgcolor: #F3F3F3"]6 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #FFF2CC"]7 CHAMPION ST [/TD]
[TD="bgcolor: #fff2cc"]tom howel[/TD]
[TD="bgcolor: #fff2cc"]jane howel[/TD]
[TD="bgcolor: #fff2cc"]julie howel[/TD]
[/TR]
[TR]
[TD]tom howel[/TD]
[TD]7 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]jane howel[/TD]
[TD="bgcolor: #F3F3F3"]7 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]julie howel[/TD]
[TD]7 CHAMPION ST[/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]Scott[/TD]
[TD="bgcolor: #F3F3F3"]11 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]Okhuizen[/TD]
[TD]12 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]Roussety[/TD]
[TD="bgcolor: #F3F3F3"]16 Champion St [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]18 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]Claro[/TD]
[TD="bgcolor: #F3F3F3"]18 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]Ferguson[/TD]
[TD]20 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]Parish[/TD]
[TD="bgcolor: #F3F3F3"]26 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]Devery[/TD]
[TD]28 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]Lehner[/TD]
[TD="bgcolor: #F3F3F3"]29 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
[TR]
[TD]Clothier[/TD]
[TD]29 CHAMPION ST [/TD]
[TD][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[TD="bgcolor: #fff2cc"][/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
G'Day Darrenmcoy,

I wrote some vba that would rebuild the data requested in your example.

Hope it helps you in your quest and enjoy.

ps Don't forget to Post a Tanks / Like if it works for you.

Code: 
Public Function TransposeExtraNames()    Dim lngMaxRow           As Long
    Dim strStreetPrev       As String
    Dim i                   As Long
    Dim lngHouseNoRow       As Long
    Dim ws                  As Worksheet
    Dim lngMaxCol           As Long
    
    Set ws = ActiveSheet
    
    lngMaxRow = ws.Range("B1048576").End(xlUp).Row                          'Change "B1048576" to "B65536" if excel  less than 2007
    
'clear existing data
    lngMaxCol = ws.Range("XFD1").End(xlToLeft).Column                       'Change "XFD1" to "IV1" if excel  less than 2007
    If lngMaxCol >= 4 Then
        ws.Range("D1", ws.Cells(lngMaxRow, lngMaxCol)).ClearContents
    End If


    lngHouseNoRow = 1
    
    ws.Range("D1") = "ADDRESS"
    ws.Range("E1") = "Name1"
    For i = 2 To lngMaxRow
        If strStreetPrev <> ws.Range("B" & i) Then                          'check if same address
            'not same house as previous
            lngHouseNoRow = lngHouseNoRow + 1
            ws.Range("D" & lngHouseNoRow) = ws.Range("B" & i)
            ws.Range("E" & lngHouseNoRow) = ws.Range("A" & i)
            strStreetPrev = ws.Range("B" & i)
        Else
            'same house as last one
            lngMaxCol = ws.Range("XFD" & lngHouseNoRow).End(xlToLeft).Column + 1            ''Change "XFD" to "IV" if excel less than 2007
            ws.Cells(1, lngMaxCol) = "Name" & (lngMaxCol - 4)                               'fill in header of another name
            ws.Cells(lngHouseNoRow, lngMaxCol) = ws.Range("A" & i)                          'add extra header
        End If
    Next i
' Clear any references from memory
    Set ws = Nothing
End Function
 
Upvote 0
using PowerQuery

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]john smith[/td][td=bgcolor:#DDEBF7]4 CHAMPION ST[/td][td][/td][td=bgcolor:#E2EFDA]4 CHAMPION ST[/td][td=bgcolor:#E2EFDA]john smith[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]don howel[/td][td]5 CHAMPION ST[/td][td][/td][td]5 CHAMPION ST[/td][td]don howel[/td][td]james jonson[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]james jonson[/td][td=bgcolor:#DDEBF7]5 CHAMPION ST[/td][td][/td][td=bgcolor:#E2EFDA]6 CHAMPION ST[/td][td=bgcolor:#E2EFDA]wayne not[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]wayne not[/td][td]6 CHAMPION ST[/td][td][/td][td]7 CHAMPION ST[/td][td]tom howel[/td][td]jane howel[/td][td]julie howel[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]tom howel[/td][td=bgcolor:#DDEBF7]7 CHAMPION ST[/td][td][/td][td=bgcolor:#E2EFDA]11 CHAMPION ST[/td][td=bgcolor:#E2EFDA]Scott[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]jane howel[/td][td]7 CHAMPION ST[/td][td][/td][td]12 CHAMPION ST[/td][td]Okhuizen[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]julie howel[/td][td=bgcolor:#DDEBF7]7 CHAMPION ST[/td][td][/td][td=bgcolor:#E2EFDA]16 Champion St[/td][td=bgcolor:#E2EFDA]Roussety[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Scott[/td][td]11 CHAMPION ST[/td][td][/td][td]18 CHAMPION ST[/td][td]Thomas[/td][td]Claro[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Okhuizen[/td][td=bgcolor:#DDEBF7]12 CHAMPION ST[/td][td][/td][td=bgcolor:#E2EFDA]20 CHAMPION ST[/td][td=bgcolor:#E2EFDA]Ferguson[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roussety[/td][td]16 Champion St[/td][td][/td][td]26 CHAMPION ST[/td][td]Parish[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Thomas[/td][td=bgcolor:#DDEBF7]18 CHAMPION ST[/td][td][/td][td=bgcolor:#E2EFDA]28 CHAMPION ST[/td][td=bgcolor:#E2EFDA]Devery[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Claro[/td][td]18 CHAMPION ST[/td][td][/td][td]29 CHAMPION ST[/td][td]Lehner[/td][td]Clothier[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Ferguson[/td][td=bgcolor:#DDEBF7]20 CHAMPION ST[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Parish[/td][td]26 CHAMPION ST[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Devery[/td][td=bgcolor:#DDEBF7]28 CHAMPION ST[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Lehner[/td][td]29 CHAMPION ST[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Clothier[/td][td=bgcolor:#DDEBF7]29 CHAMPION ST[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Group = Table.Group(Source, {"ADDRESS"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "NAME", each Table.Column([Count],"NAME")),
    Extract = Table.TransformColumns(List, {"NAME", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "NAME", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"NAME.1", "NAME.2", "NAME.3"})
in
    Split[/SIZE]
 
Upvote 0
Thanks, heaps. I am still working out how to implement this.. Ill let you know if I can make it happen.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
L
  • Solved
Using INDEX...SMALL...IF...ROW and COUTNIF in order to match multiple peoples names to single email for mail marge
Replies
6
Views
1K
AhoyNC
A
M
  • Solved
Looking for the best way to transpose data from a single row into multiple rows
2
Replies
11
Views
1K
DanteAmor
DanteAmor
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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