Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing text (2000)

    Hi, I have a text file that I imported into excel. My problem is that the info I need is in A1 and A2, E1and E2, and F1 and F2. How can I get the info in A2,E2, and F2 up to Row 1 so I can insert a header and do a sort? The file is from A8000:H8000.
    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text (2000)

    I am not sure what you are asking. If you delete cell A1 (using Delete on the Edit menu, not the delete key), Excel will display a dialog box. If you click "Shift cells up" in that box, Excel will shift A2, and all cells below up one row putting A2 in A1. Is that what you want?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text (2000)

    The infomation in need is in A1 and in A2. The Last name and first name and other info imported into row A. The rest of the info I need like DOB and Address imported into row B. The import did this all the way down to 8000. It also left every 3rd row empty. I have attacked a file.

  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: Importing text (2000)

    If I understand you correctly and your pattern continues try this (on a COPY of your sheet!):
    Insert 2 columns at the front (New Col A&[img]/forums/images/smilies/cool.gif[/img]
    In B1, enter 1, in B2, 2, in B3, 3
    In B4 enter:
    <pre>=B1</pre>

    Copy B4 from B5 thru the end of the data in (New) col C = (old Col A)

    In A1 enter:
    <pre>=row()</pre>

    and copy it to A2 to end of data rows (or autofill)
    Select cols A&B, copy, paste - special values
    You now have in COl A 1-whatever, and in B a collection of 1s, 2s, 3s. 1s are the first/last name and base, 2s are date and state, 3s should all be blank.

    Data - sort (No header), by col B , then Col A. This will group all the 1s together, then the 2s then the 3s.
    Delete the rows at the end 3s (blank), move the 2 cols of data in row2 to Col F/g
    Delete cols A and B

    You should now have 1 row with FN, LN, "Base", Date, State for each item sorted in the same order you had before.
    Insert a row at the top, Add your headers, and you are good to go.

    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
  •