Moving Conditional data from column to column

jlbecker67 · Mar 27th, 2002, 02:39 PM

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

MrExcel · Mar 27th, 2002, 08:46 PM

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 ]

Yogi Anand · Mar 27th, 2002, 09:08 PM

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

jlbecker67 · Mar 28th, 2002, 07:19 AM

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 ]

Yogi Anand · Mar 30th, 2002, 11:57 PM

Quote:

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!

jlbecker67 · Apr 1st, 2002, 09:33 AM

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

Yogi Anand · Apr 3rd, 2002, 09:38 PM

Quote:

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 ]

Mar 27th, 2002, 09:08 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 02:39 PM	#1
jlbecker67 New Member Join Date: Mar 2002 Posts: 8	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

Mar 27th, 2002, 08:46 PM	#2
MrExcel . Join Date: Feb 2002 Location: Akron, Ohio USA Posts: 789	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 ]

Mar 28th, 2002, 07:19 AM	#4
jlbecker67 New Member Join Date: Mar 2002 Posts: 8	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 ]

Apr 1st, 2002, 09:33 AM	#6
jlbecker67 New Member Join Date: Mar 2002 Posts: 8	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