Probably very easy- but I'm learning excel

jbyrne · Mar 24th, 2002, 01:34 PM

I have a ascii file that i have to open. The file contains name, address city state and zip. Is there a macro to open the file, format each column in the text mode, keeping all the states in the state column except NM, ME, MA, AZ. (putting those states on a seperate sheet).

If possible a brief explanation of how to revise it say if the state column is in a different column.

Thanks

Yogi Anand · Mar 24th, 2002, 02:44 PM

Quote:

On 2002-03-24 12:34, jbyrne wrote:
I have a ascii file that i have to open. The file contains name, address city state and zip. Is there a macro to open the file, format each column in the text mode, keeping all the states in the state column except NM, ME, MA, AZ. (putting those states on a seperate sheet).

If possible a brief explanation of how to revise it say if the state column is in a different column.

Thanks

Hi jbyrne:
You can open a text file and you can have your data convered into cells intersected by columns and rows.
My suggestion is that you go one step at a time --
1. Open your text file in Excel
2. Make sure every thing is in appropriate cells
3. Filter your data by states, copy filtered data onto clipboard
4. Paste the copied data into another worksheet, and so on

HTH
Please post if it works for you ... otherwise explain a little further and let us take it from there!

jbyrne · Mar 25th, 2002, 09:48 AM

Hi Yogi,

Thanks for your response. Yes, that did work. Is there a macro that would remove certain states for me.

Is there a formula/macro where I can tell excel to look in the state column and say keep all of the following states: NJ NY PA SC NC and put all other states on sheet2?

Thanks

Dave Patton · Mar 25th, 2002, 10:58 AM

You stated

"Is there a formula/macro where I can tell excel to look in the state column and say keep all of the following states: NJ NY PA SC NC and put all other states on sheet2? "

A) You could use Data Filter with multiple criteria.
or
B) You could put a formula in a free column that provides True or False or a code if you you want to select more than 2 groupings.
Filter on this column.

Formula =OR(A2={"NJ","NY","PA","SC","NC"})

gives a result of True or False

[ This Message was edited by: Dave Patton on 2002-03-25 10:38 ]

[ This Message was edited by: Dave Patton on 2002-03-25 10:39 ]

Yogi Anand · Mar 25th, 2002, 12:02 PM

Hi Dave:
I like that. For data filtering you mentioned multiple criteria ... depends on what you mean by multiple criteria -- I used the criteria as F9:F14 (with state in F9, NJ in F10, NY in F11, PA in F12, NC in F13, and SC in F14)

state
NJ
NY
PA
NC
SC

Have you tried using a formula for the filter criteria? Just a thought!
Well jbyrne -- Dave has put the final pieces together for this project.

jbyrne · Mar 26th, 2002, 12:03 PM

Thanks to all who helped me. I love using this site!! Helpful people like you all make this a great site!!

Mar 25th, 2002, 12:02 PM	#5
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Dave: I like that. For data filtering you mentioned multiple criteria ... depends on what you mean by multiple criteria -- I used the criteria as F9:F14 (with state in F9, NJ in F10, NY in F11, PA in F12, NC in F13, and SC in F14) state NJ NY PA NC SC Have you tried using a formula for the filter criteria? Just a thought! Well jbyrne -- Dave has put the final pieces together for this project. __________________ 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 24th, 2002, 01:34 PM	#1
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	I have a ascii file that i have to open. The file contains name, address city state and zip. Is there a macro to open the file, format each column in the text mode, keeping all the states in the state column except NM, ME, MA, AZ. (putting those states on a seperate sheet). If possible a brief explanation of how to revise it say if the state column is in a different column. Thanks

Mar 25th, 2002, 09:48 AM	#3
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	Hi Yogi, Thanks for your response. Yes, that did work. Is there a macro that would remove certain states for me. Is there a formula/macro where I can tell excel to look in the state column and say keep all of the following states: NJ NY PA SC NC and put all other states on sheet2? Thanks

Mar 25th, 2002, 10:58 AM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	You stated "Is there a formula/macro where I can tell excel to look in the state column and say keep all of the following states: NJ NY PA SC NC and put all other states on sheet2? " A) You could use Data Filter with multiple criteria. or B) You could put a formula in a free column that provides True or False or a code if you you want to select more than 2 groupings. Filter on this column. Formula =OR(A2={"NJ","NY","PA","SC","NC"}) gives a result of True or False [ This Message was edited by: Dave Patton on 2002-03-25 10:38 ] [ This Message was edited by: Dave Patton on 2002-03-25 10:39 ]

Mar 26th, 2002, 12:03 PM	#6
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	Thanks to all who helped me. I love using this site!! Helpful people like you all make this a great site!!