Thanks:  0
Likes:  0

# Thread: Moving Conditional data from column to column

1. 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. 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. 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

4. 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. 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!

6. 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. 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

#### Posting Permissions

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