Names and Address columns to rows?

[darrenmccoy]

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>

NAME	ADDRESS		ADDRESS	Name1	Name2	Name3
john smith	4 CHAMPION ST		4 CHAMPION ST	john smith
don howel	5 CHAMPION ST		5 CHAMPION ST	don howel	james jonson
james jonson	5 CHAMPION ST		6 CHAMPION ST	wayne not
wayne not	6 CHAMPION ST		7 CHAMPION ST	tom howel	jane howel	julie howel
tom howel	7 CHAMPION ST
jane howel	7 CHAMPION ST
julie howel	7 CHAMPION ST
Scott	11 CHAMPION ST
Okhuizen	12 CHAMPION ST
Roussety	16 Champion St
Thomas	18 CHAMPION ST
Claro	18 CHAMPION ST
Ferguson	20 CHAMPION ST
Parish	26 CHAMPION ST
Devery	28 CHAMPION ST
Lehner	29 CHAMPION ST
Clothier	29 CHAMPION ST

<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>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Brombrough]

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.

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

 

[sandy666]

using PowerQuery

NAME	ADDRESS		ADDRESS	NAME.1	NAME.2	NAME.3
john smith	4 CHAMPION ST		4 CHAMPION ST	john smith
don howel	5 CHAMPION ST		5 CHAMPION ST	don howel	james jonson
james jonson	5 CHAMPION ST		6 CHAMPION ST	wayne not
wayne not	6 CHAMPION ST		7 CHAMPION ST	tom howel	jane howel	julie howel
tom howel	7 CHAMPION ST		11 CHAMPION ST	Scott
jane howel	7 CHAMPION ST		12 CHAMPION ST	Okhuizen
julie howel	7 CHAMPION ST		16 Champion St	Roussety
Scott	11 CHAMPION ST		18 CHAMPION ST	Thomas	Claro
Okhuizen	12 CHAMPION ST		20 CHAMPION ST	Ferguson
Roussety	16 Champion St		26 CHAMPION ST	Parish
Thomas	18 CHAMPION ST		28 CHAMPION ST	Devery
Claro	18 CHAMPION ST		29 CHAMPION ST	Lehner	Clothier
Ferguson	20 CHAMPION ST
Parish	26 CHAMPION ST
Devery	28 CHAMPION ST
Lehner	29 CHAMPION ST
Clothier	29 CHAMPION ST

[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]

 

[darrenmccoy]

Thanks, heaps. I am still working out how to implement this.. Ill let you know if I can make it happen.