Moving Conditional data from column to column
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Moving Conditional data from column to column

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

    Default

     
    Maybe this is simple, but I am not very good with excel. I want to write a formula to move data to another cell based on certain criteria.
    Example: I have a spreadsheet with our county's property tax values with addresses. The addresses didn't split into columns right. Sometimes the street (st) got moved to the wrong column because there wasn't a direction on the address. How do I move all these two letter designations to the right columns without a lot of time?

    Thank you very much,
    Jerry

  2. #2
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    858
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    Couple of ways to do this:

    Let's say your data is in A1:K1000

    Add a column L with a formula: =IF(LEN(G2)=2,"MOVE ME","OK ALREADY"). Copy this formula from L2 to L3:L1000.

    In column M, add a sequence number that runs from 1 in Cell M2 to 999 in M1000.

    Sort A1:M1000 by column L. You now have all of the "wrong" records in a block at the top of the worksheet. Once they are in a block, you can Cut and paste from G2:G100 to H2:H100.

    Then, sort A1:M1000 by column M to get everything back in the right order. Delete your columns L & M.

    (The other way is with a VBA macro - the above is simpler, unless you have to do this every day)

    Bill

    _________________
    MrExcel.com Consulting

    [ This Message was edited by: MrExcel on 2002-03-27 19:47 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jlbecker67:
    Another variation towards the end of the excellent solution proposed by Bill, after you have got all the addresses corrected, and say your State Column is in the wrong order ...
    see sample below:

    1name 2street 4state 3city 5zip
    Smith 101 Bay MI Detroit 48226
    Keller 200 Kelly OH Akron 11111
    Baylor 111 Kate MI Troy 48098

    The right order would of entries would have been
    1name
    2street
    3city
    4state
    5zip

    At this point, you can sort the affected subset of records, with the option of sorting records LEFT to Right -- (that's why you will notice, I changed the field headers to have 1,2,3,4, and 5 as the first character)

    then your sorted subset will look like as:

    1name 2street 3city 4state 5zip
    Smith 101 Bay Detroit MI 48226
    Keller 200 Kelly Akron OH 11111
    Baylor 111 Kate Troy MI 48098

    HTH
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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

    Default

    Maybe I didn't explain the problem correctly.
    Example:
    John Doe.....123.....W.........Main.....St
    Jane Doe.....987.....Nowhere...St

    How can I do move all the "St" 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

    [ This Message was edited by: jlbecker67 on 2002-03-28 06:22 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 06:19, jlbecker67 wrote:
    Maybe I didn't explain the problem correctly.
    Example:
    John Doe.....123.....W.........Main.....St
    Jane Doe.....987.....Nowhere...St

    How can I do move all the "St" 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

    [ This Message was edited by: jlbecker67 on 2002-03-28 06:22 ]
    Hi Jerry:
    I believe Bill and I have given you two of several workable solutions. It does need working at it to get a good grasp on things. I don't think I follow what you are asking now. If you post a sample of your worksheet data or email it to me, I will be glad to look at it!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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

    Default

    I have gotten everything sorted but it left some leading spaces. I am now trying to figure out how to get rid of them quickly and easily. Any ideas? Thanks for all the help.

    Jerry

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-01 08:33, jlbecker67 wrote:
    I have gotten everything sorted but it left some leading spaces. I am now trying to figure out how to get rid of them quickly and easily. Any ideas? Thanks for all the help.

    Jerry
    Hi Jerry:
    try the trim function ... if there are extra spaces in cell A1, then
    =TRIM(A1)
    would remove the extra spaces!


    [ This Message was edited by: Yogi Anand on 2002-04-03 20:39 ]

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