Probably very easy- but I'm learning excel

[jbyrne]

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]

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]

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]

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]

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]

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