Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Probably very easy- but I'm learning excel

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    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-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!
    Regards!

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  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

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



Some videos you may like

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
  •