Moving Conditional data from column to column, repost
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Moving Conditional data from column to column, repost

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How can I move certain data from one column to another? Example:
    John Doe.....123.....W...........Main.....St
    Jane Doe.....987.....Nowhere.....St

    As you can see the street name is shifted left because of the absence of the direction designation. How can I do move all the "St"'s in the wrong column to the right one and then move the street name to the right column? Can I have a formula that finds the two letter designations and moves them to the column to the right? And then have one that finds all data with more than two letters (for the street name) and move it to the column to the right?
    Thanks for your help. Sorry I am so ignorant.

    Jerry

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jerry,

    This cannot be done with a formula, since a formula cannot insert cells, rows, or columns to a worksheet. It requires a macro (in this case, a fairly simple one). If this is to be completely general, there may be some special cases that you should think about, such as the street naming in Kittredge Colorado where the streets are Avenues name A-Z. So in the street "E Avenue" the E does not stand for East, but is the name of the street. In some areas, the north-east-south-west designator is given last, such as "72nd Ave W". Do you intend to handle this sort of thing?

    Anyway, here is a macro that just looks at every cell in column E, and when it finds one empty shifts the cells C and D on that row one cell to the right. Note that it starts with row 2 assuming that there is a header in row 1.

    Sub ShiftAddress()
    Dim iRow As Long
    'start at row 2 because of header in row 1
    For iRow = 2 To [a65536].End(xlUp).Row
    If IsEmpty(Cells(iRow, 5)) Then
    'shift over the cells in columns C and D
    Cells(iRow, 3).Insert shift:=xlToRight
    End If
    Next iRow
    End Sub
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks, Damon. That worked great.

    Jerry

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com