1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Can Excel automatically arrange columns for me?

Discussion in 'Business Applications' started by stueycaster, Oct 20, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. stueycaster

    stueycaster Thread Starter

    Joined:
    Aug 8, 2004
    Messages:
    111
    I have a rather large file that I would like to print. I have other files like it that were done in Excel and they came out just like I wanted them to. The entries are arranged in rows and columns. I was able to number the rows and put headers over each column. The one in question is arranged in rows but not in columns. I can open it in Excel but I have no idea how to get it to arrange into columns. Is there any way Excel can do this for me? I'd really rather not do this manually because there are over three thousand rows that need to be in 12 columns.
     
  2. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Can you describe better what you mean by "arrange"?
    Better still if you can attach a small sample spreadsheet and then use it as a reference during your description?
     
  3. stueycaster

    stueycaster Thread Starter

    Joined:
    Aug 8, 2004
    Messages:
    111
    I'm sorry. I tried to give you the spread sheet that you asked for but the webpage squashed the spacing. When I typed it in the spacing was just right. Then I posted it and it was changed. I don't know how to make it work. Oh well.
     
  4. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Create it as an actual XLS spreadsheet.
    Then, from Explorer, rename the extension from XLS to TXT.
    Then, use the Reply button, and at the bottom is a Manage Attachments.
    Select it, and attach the TXT sheet.

    Then, we can d/l it, rename it to XLS and see it for real in Excel.
     
  5. stueycaster

    stueycaster Thread Starter

    Joined:
    Aug 8, 2004
    Messages:
    111
    Ok here it is
     

    Attached Files:

  6. ChuckE

    ChuckE

    Joined:
    Aug 30, 2004
    Messages:
    2,311
    Have you looked into Excel Help? I did, and the simple answer was to copy the desired cells, then "Paste Special... " select "Transpose"

    You can either paste into a blank area of the selected area, and remove the unwanted areas, or just Paste into a new sheet.
     

    Attached Files:

  7. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Ok, I have the spreadsheet now. But, using the content as a reference, can you please explain what you want to do?
    (given it's different than what ChuckE has suggested)
     
  8. stueycaster

    stueycaster Thread Starter

    Joined:
    Aug 8, 2004
    Messages:
    111
    I clicked on the little arrow next to the paste button and Transpose is grayed out. I'm sorry but I Don't know how to use office programs.

    I've attached a little piece the file that I want to convert. What I'd like to do is to convert the one labeled Kidd1 to a file like the one in Raines1.

    Thank you for your patience.
     

    Attached Files:

  9. ChuckE

    ChuckE

    Joined:
    Aug 30, 2004
    Messages:
    2,311
    Looking at your Kidd1 file. You DON'T WANT to transpose the data because you have everything in one column. You have a full person's data in just one cell. That is just not the way to do it.

    For example on one cell you have:
    KIDD A B na 1917 10 20 GOOCH ANNIE LNCLN 104 51729
    and in the next cell you have:
    KIDD AALIYAH MARIE FEMALE 2001 04 23 KIDD STACY N PULASKI 034 16778

    What you want to do is have at least 14 cells (across) to hold one small snippet of each small part.
    The titles of the 14 columns might be (and I am guess as to what the data is):
    Last name, First name, Middle name, gender, Born-Yr, Born-Mo, Born-Day, etc.... (actually those three date portions should be all in one cell, as a single date).

    I would love to help you, but you first need to get this data in a better format.
    You can program a search-replace to get this data in better shape. I can help you with that, as well, but I am not going to bore the readers here.

    Here is a clue, at least the way I would do it (and there are many ways),
    I would get all the data into a Word doc, search for 3 spaces (seems to be what you used) and replace with a comma.
    Then import all that data into an Excel spread sheet.
    There is still going to be a lot of editing to do, since some of the data doesn't have 14 portions of data.

    Finally select it all and transpose it, if that is still what you want to do.
    (I wouldn't though, because those columns is a good way of keeping this sort of data.)

    So, anyway, here is the result of doing something like that, and I have combined the yr/mo/day data into one cell. I also put a title on the top of each column, but I may have guessed wrong on what that data actually is.

    Let me know if you want to learn the steps of doing this with your real data. It isn't too hard, and you probably can do it all in just a few minutes.
     

    Attached Files:

  10. stueycaster

    stueycaster Thread Starter

    Joined:
    Aug 8, 2004
    Messages:
    111
    What you did with the Kidd2.txt file is exactly what I've been wanting to do all along. The file came to me as an E-mail attachment just the way it is. I have no clue how to do what you did with it. I'd rather not type it all into a spread sheet because there are over three thousand people in this list. If you can tell me how to convert it automatically I'd appreciate it a lot.
     
  11. ChuckE

    ChuckE

    Joined:
    Aug 30, 2004
    Messages:
    2,311
    I have responded to Stuey via email, but the basic general steps are to:
    1) select all pertinent cells.
    Because you have many portions of data all entered into one cell, with spaces between, all you need to do is select the top cell, scroll to the bottom cell and hold SHIFT when selecting that last cell. All the cells (it's just 1 wide, many tall) will be selected.
    2) COPY (command Edit>Copy).
    3) open a Word document and Paste Special, Unformatted Text.
    4) in that Word doc search for all occurrences of 3 spaces, and replace with a comma.
    4b) also some lines you have a leading space, you might want to remove them.
    5) File>Save As>Plain Text which will have a default name extension of .txt, just change it to .csv
    6) Now open up that .csv file (Excel should automatically be used).
    7) Because you have the birth date split up in 3 columns (E,F,G) (year, month, day) you need to create a new column to combine them. So insert a new column to the left, of those 3. That becomes the new column E, since the other 3 have now become F,G,H.
    8) put the formula in cell E1=DATE(F1,G1,H1) (You will see the respective date now combined in that one cell)
    9) now duplicate that formula all down through all of column E. You will now see the dates all brought into their prospective cells.
    10) now you want to remove the formulas but keep the data, so select those same E column cells and Edit>Copy
    11) still having those same cells selected, do a Edit>Paste Special>"Values and number formats"
    12) now you want to remove the unneeded 3 columns F,G and H. So select them and Edit>Delete

    That's just about it. There is a little bit more clean up, and formatting, but nothing you shouldn't be able to handle.
     
  12. stueycaster

    stueycaster Thread Starter

    Joined:
    Aug 8, 2004
    Messages:
    111
    Chuck you are da man. It worked. Thanks a lot. You guys at this support forum have saved me a bunch of times now. I really don't know where I would have been without you. Probably sitting in front of a virus and spyware infested piece of junk with no clue how to operate it. Thanks again.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/287066

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice