Names and Address columns to rows?

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
21
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>
NAMEADDRESSADDRESSName1Name2Name3
john smith4 CHAMPION ST 4 CHAMPION ST john smith
don howel5 CHAMPION ST 5 CHAMPION ST don howeljames jonson
james jonson5 CHAMPION ST 6 CHAMPION ST wayne not
wayne not6 CHAMPION ST 7 CHAMPION ST tom howeljane howeljulie howel
tom howel7 CHAMPION ST
jane howel7 CHAMPION ST
julie howel7 CHAMPION ST
Scott11 CHAMPION ST
Okhuizen12 CHAMPION ST
Roussety16 Champion St
Thomas18 CHAMPION ST
Claro18 CHAMPION ST
Ferguson20 CHAMPION ST
Parish26 CHAMPION ST
Devery28 CHAMPION ST
Lehner29 CHAMPION ST
Clothier29 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>
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Brombrough

New Member
Joined
Apr 10, 2017
Messages
49
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,428
using PowerQuery

NAMEADDRESSADDRESSNAME.1NAME.2NAME.3
john smith4 CHAMPION ST4 CHAMPION STjohn smith
don howel5 CHAMPION ST5 CHAMPION STdon howeljames jonson
james jonson5 CHAMPION ST6 CHAMPION STwayne not
wayne not6 CHAMPION ST7 CHAMPION STtom howeljane howeljulie howel
tom howel7 CHAMPION ST11 CHAMPION STScott
jane howel7 CHAMPION ST12 CHAMPION STOkhuizen
julie howel7 CHAMPION ST16 Champion StRoussety
Scott11 CHAMPION ST18 CHAMPION STThomasClaro
Okhuizen12 CHAMPION ST20 CHAMPION STFerguson
Roussety16 Champion St26 CHAMPION STParish
Thomas18 CHAMPION ST28 CHAMPION STDevery
Claro18 CHAMPION ST29 CHAMPION STLehnerClothier
Ferguson20 CHAMPION ST
Parish26 CHAMPION ST
Devery28 CHAMPION ST
Lehner29 CHAMPION ST
Clothier29 CHAMPION ST

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]
 

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
21
Thanks, heaps. I am still working out how to implement this.. Ill let you know if I can make it happen.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,625
Messages
5,512,473
Members
408,899
Latest member
cve60069

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top