Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Excel 2007 (SP 1)

    I output data from one program (in CSV) with the columns going chronologically left to right.

    I copy that in to Excel, and output new columns of modified data.

    I'd like those new columns to go chronologically from right to left.

    Suggestions?

  2. #2
    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: Excel 2007 (SP 1)

    After pasting into excel sort the columns
    Data - sort
    [options] press sort left to right
    Then choose "descending"

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Excel 2007 (SP 1)

    Sorry - I wasn't clear enough.

    I need to maintain the original columns in their left-to-right order.

    I do this repetitively, so I add new columns to the right (and occasionally update the entire set of columns).

    Each time I add say n new columns to the right, I need to add n to the left. So I might have BA to BF filled with raw data going left-to-right, and I have modified data in AU to AZ ordered from right-to-left. Then I add two more columns of raw data so that it goes from BA to BH, and the modified versions of those columns will go into AT and AS.

  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: Excel 2007 (SP 1)

    I am afraid I don't understand what you are after. Could you elaborate, perhaps adding a sample workbook with a before sheet and an after sheet?

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Excel 2007 (SP 1)

    Here goes.

    This is a file I keep grades in. Because it is grades, I try to keep everything for each student in its own row in a single worksheet. This includes both the raw scores and scratch work (which they don't see), and the scaled and curved grades which they do. The spreadsheet itself isn't available to students, but I do use it to output HTML files based on named ranges that they can view.

    I have other software that outputs scores for parts of the class. I can't change the fact that it output in chronological order from left to right. Because these outputs add columns as we proceed through the semester, the easiest way for me to get them into my grade file is simple copy and paste of the entire file. In this example, columns EP to FF are copied from such a CSV file. If I do another assignment with that software, it will generate one more column of data, so I'd insert a column to the right of FF, and enter the new data there. I do some scaling of that data, and put the results in FH though FV - in chronological order from left to right. It's important for me to keep EP to FF in that raw form so that if there is a problem, I can go back to the original CSV file from the other program and double-check things.

    But, students like to see their grades in reverse chronological order - so that they don't have to scroll to see their most recent work. So, EB repeats FH (they only see EB, and I see both). But, to do the reverse chronological order, EA must show the same numbers as FI. (Note that it skips FI and goes to FJ in this file due to a software glitch in the other software).

    As you can see, in this (older) file my best method was to hand type a formula into EB, EA and so on.

    Since then, I've tried to do this with =offset() with some success (not shown here). I am looking for a more flexible method, because sometimes raw data that comes in as one range of columns is displayed for students in different sections. In part, this problem shows up with FH to FJ which could be shown in reverse order fairly easily in EB, EA, and DZ with =offset(). That's more of a problem when I actually need to skip something and put FH into EB, skip FI, and put FJ into EA, and so on.

    More broadly, I have assignments with grades coming from different applications. So, I have to skip intervening columns: FM maps to DH, FN to DG and so on.

    So, what I'm looking for is 1) a plain vanilla way to do the reverse ordering that might be simpler than =offset(), and 2) a more general way than =offset() to deal with more complex mappings.
    Attached Files Attached Files

  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: Excel 2007 (SP 1)

    I am still not sure exactly what you are after, but on simple examination, I would recommend creating a database type structure for storage (in Access preferably, but probably could be done in Excel) and then create sheets to read the info from the sheet(s) in the form you desire.

    If setup correctly you may be able to a use pivottable(s) to summarize the results directly

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Excel 2007 (SP 1)

    Thanks for the suggestion, but that seems to me to be a big investment for the scale of the inconvenience I'm dealing with.

    Perhaps =offset() really is the best way to go.

  8. #8
    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: Excel 2007 (SP 1)

    That all depends on what you use it for. If set up correctly data entry will be easier for you and summary tables and graphics could be generated directly...

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Excel 2007 (SP 1)

    I read the post but do not have Excel 2007 so I did not see the attachment.

    Possible solution

    Aywhere left of the raw data find an area large enough for the final results. Just above the first row of live data leave room for a row you will need.
    The new row will be the Sort Key used by Excel. That number can be a reference to the raw data, a number you enter, or a copy value from the raw data. If it is a reference it must be an absolute such as $EP$10.

    In the new area for the first student put in a reference to the raw data using a column absolute such as $EP11. Repeat for every possible column (even if it may turn up later as blank). In other words be generous so that you will do this once and use it all year.
    Copy the formulas down for each student. In effect you have created a mirror
    Now in the raw data area in the new row enter the chronological number for that col, for blank cols leave blank or enter -0-.

    Go back to area left of raw data.
    Select the entire new area for all rows and cols with formulas. Make sure to include as the frist row the Sort Key row!
    Use the Sort Function, select Options sort from Left to Right, next Select Decending sort for the Sort Key row.
    The data will be sorted RIght to Left and all Blank Cols will land up to the far left out of your way.
    Of course you can also resort to get back to the original sheet just in case more raw data were to arrive.

    The trick is that blank cols sort to the end. Also some planning of how many cols could possible be used (blanks included) before you raw data arrvies means you set this up oncce and use it all year. In addition by changing any Col Sort Key to Zero it too will land up out of your way. This gives you great power to inlcude or exclude any Col by merely keeping its order number of using Zero. negatvie numbers may also be used in the Sort Key row.

    Hope this helps.

    Regards,

    Tom Duthie

Posting Permissions

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