+ Reply to Thread
Results 1 to 12 of 12

Arranging information into tables

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    8

    Arranging information into tables

    Hi there,

    I have a problem with Excel. I don't use it a lot but I am trying to carry out a seemingly simple task. I may need to get a macro written but you may be able to advise better as I am not very experienced.

    I have a table of info as shown below (in sheet one)

    ssc.jpg

    In sheet two I am looking to organize the first column (items) by the associated label in column 2. Basically I want 5 columns in sheet two with column 1-all A's, column 2 all B's, column 3 all C's etc.

    When I have tried to do it I use the IF function but I was left with a zero in the space where a false result is returned.

    I hope this makes sense but please just ask and I can send on more info or a detailed Excel sheet to you.

    Thanks in advance
    James

  2. #2
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Arranging information into tables

    The attached cheats by adding a helper column in your first sheet. If this doesn't work, there are other solutions, but this is easiest.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-17-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Arranging information into tables

    Hi ya BOB,

    works like a charm mate but I have a lot of data so the helper column may be laborious.

    Would it be a lot more effort to work a solution without this column??

    Thanks for the solution- its great,

    Cheers

  4. #4
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Arranging information into tables

    I played around with this for a bit and can't seem to overcome the need for a helper column. Since the helper column simply has a formula that you can copy down, I'm not understanding why adding it "can be laborious". If you can help me understand that, I might be able to help. For example, if it';s a problem adding the helper column to the left of your data, you can use something other than VLOOKUP. But I need to know the constraints.

  5. #5
    Registered User
    Join Date
    07-17-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Arranging information into tables

    Hi BOB,

    my mistake. The helper column as you said is a formula so it does exactly as you have said. Apologies for that. I have however hit another problem. I tried to run it on excel 03 and it doesn't recognize it. Is this a general problem do you know?? Its not a huge one as I am getting my other machine updated soon enough but it was just to get it to work immediately on the older machine.

    Thanks for your help on this and apologies again.

    Cheers,
    James

  6. #6
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Arranging information into tables

    No problem. IFERROR doesn't work in 2003, so please use the attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-17-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Arranging information into tables

    Hi again Bob,

    this problem is expanding. I now have multiple sheets of data to sort into columns on one summary sheet. I am thinking that this is getting a bit too out of control for excel as I tried to manipulate the original sheet you posted but to no avail. I tried to copy the sorted columns into the final summary sheet but no success.

    If you think you can help it would be greatly appreciated.

    Thanks in advance,

    Attachment 169212

    Cheers,
    James
    Attached Files Attached Files

  8. #8
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Arranging information into tables

    So you want the list to continue for multiple sheets? So, using your example, Sheet 1 has 3 As and Sheet 2 has 2 As, so you want all five on the Summary? That's possible, but it would be useful to know how many sheets total you will need. Of course, combining all your data on one data sheet is preferred for any number of reasons, but if this is the need then okay, we can deal with it.

  9. #9
    Registered User
    Join Date
    07-17-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Arranging information into tables

    Hi Bob,

    yeah the idea would be that 5 sheets of data would be grouped and listed into one summary sheet so thats six sheets in the total workbook. The summary again would hold each column A-E which is the sorted data from the other 5 'score' sheets.

    I am also still using the '03 Excel package just to let you know.

    Cheers,
    James

  10. #10
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Arranging information into tables

    Try this version.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-17-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Arranging information into tables

    Looks excellent however I can't access it in the 2003 Excel...

    Cheers,
    James

  12. #12
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Arranging information into tables

    Sorry. Your profile shows Excel 2007. Try this.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1