Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Break Single Line into Table Format? (2003)

    Hello,

    Please look at the attached Excel File. It has some Network User ID's with their permissions to access a shared resource.

    I want to put each user ID on a new line and the permission for it beside it, i.e. convert this single line into a table format.

    How can I use Excel to help?

    Thanks,

    BD.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break Single Line into Table Format? (2003)

    Wow, that's really cool.

    Problem is I need help near the end.
    Tried to follow your steps, but got result as in attached Excel File.

    Where could I be going wrong?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Break Single Line into Table Format? (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Sep-04 08:17. corrections added)</P>Relatively simple way:
    Select cell A1
    Data text to columns <finish>
    [This will put each item into its own column]
    Select A1:Al1
    ctrl-c
    select A2
    edit -paste special - transpose <ok>
    You now have all the columns in rows
    <font color=red>ctrl-c</font color=red>
    select B1 ctrl-v
    You now have a "duplicate column" offset by 1 row
    Cols A and B are what you want by you want only everyother row;
    delete row 1
    data - filter - autofilter
    select pulldown on column A
    Custom
    "Begins with"
    (no quotes "+")
    Now you have displayed the "bad rows"
    Select them and delete <font color=red>the rows</font color=red>
    remove the autofilter
    (data - filter -autofilter )

    And you have what you want.

    If you do it routinely you could use a macro (macro recorder should be able to do 80% of the coding - the selecting A1:Al1 would need to be generalized to get all the columns)

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Break Single Line into Table Format? (2003)

    1) Missing column B: it looks like you didn't create the offset column by copying the transposed entries into column B (you should get 2 cols of data) {I forgot a ctrl-c (sorry)
    2) blank rows: you did not delete the rows, you only deleted the cell contents

    I modified the directions to be more explicit and fix my oversight

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break Single Line into Table Format? (2003)

    Dear Steve,

    The attached screenshot shows what I'm trying to achieve, i.e. the end output.
    I.e. a table with the user ID in one column & it's corresponding permission in the other.

    I appreciate your efforts to help here.
    I didn't understand the line 'Now you have displayed the "bad rows"'
    I wasn't sure how you defined "bad" so I don't know which rows to delete here.

    Please advise. Cheers,

    BD.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Break Single Line into Table Format? (2003)

    I understand what you want. At what step does my description not match what you are getting? could you show that screenshot.

    Concerning display of the "bad rows"
    After you have done the steps I outlined you should have a filtered list (row A has autofilter "pulldown tabs") and only rows even rows from 2-38 are displayed (2,4,6,8, etc) All the items in these row begin with a "+", these are the "bad rows"

    By select them and delete the rows I mean:
    Select the "row label 2" (the entire row is selected)
    Holding <shift>, select the "row label 38 " and the "bad rows" are selected
    Right- click on this range and select "delete row"

    Then you finish up by data-filter - autofilter to remove the autofilter and display the "good rows"

    Steve

Posting Permissions

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