Results 1 to 3 of 3

Thread: Moving data from column B:B into new columns?

  1. #1

    Moving data from column B:B into new columns?

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I needed a fix for a problem I'm currently being asked to solve but simply cannot find a solution.
    On column A, I have a list of names. On column B, I have their address.
    Sometimes, however, a person has more than one address and I wanted to move the addresses to new columns titled "Property 1, Property 2, and so on".
    If anybody could help me with it, I'd greatly appreciate it.
    I have attached a sample workbook to make things easier!

    Thank you so much guys!
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    Try this:

    In C2, add a helper formula to fill all cells with a name...


    copied down.

    Then in D2 enter formula to get unique names:


    adjust ranges to suit, then confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.

    In E2 enter similar formula:


    again, adjusting ranges to suit, then confirm with CTRL+SHIFT+ENTER and copy down and across the columns.\

    Note: Do not use whole column references, and use minimum sizes possible to avoid processor slow downs due to array formulas.

  3. #3
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    mineral city oh usa
    Excel Version
    Hi Yet

    This Code is in the attached and appears to do as you require...CTRL + x will fire the Code.
    Option Explicit
    Sub Properties()
       Dim ws           As Worksheet
       Dim LR           As Long
       Dim i            As Long
       Dim cnt          As Long
       Dim cel          As Range
       Dim LastVal      As String
       cnt = 1
       Set ws = Sheets("Sheet1")
       Application.ScreenUpdating = False
       With ws
          LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
          .Range("A1:A" & LR).Select
          LastVal = ""
          For Each cel In Selection.Cells
             If Trim(cel.Value) <> "" Then
                LastVal = cel.Value
                If LastVal <> "" Then cel.Value = LastVal
             End If
          Next cel
          .Range("B1").Value = "Property 1"
          .Range("B1").AutoFill Destination:=Range("B1:E1"), Type:=xlFillDefault
          For i = LR To 2 Step -1
             If .Range("A" & i - 1).Value = .Range("A" & i).Value Then
                .Range("A" & i - 1).End(xlToRight).Offset(0, 1).Resize(1, cnt).Value = .Range("A" & i).Offset(0, 1).Resize(1, cnt).Value
                cnt = cnt + 1
                .Range("A" & i).EntireRow.Delete
             End If
          Next i
       End With
       Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts