Sorting out columns in Excel

Discussion in 'Mac Apps and Mac App Store' started by GimmeSlack12, Dec 6, 2010.

  1. GimmeSlack12 macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #1
    Ok I want to sort out my finance spreadsheet where I have the category of the spending and the adjacent cell is the amount I spent.

    [Category, Price]
    [Gas , $50]
    [Grocery , $80]
    [Lunch , $15]
    [Gas , $34.50]

    What I'd like is for a function that can sort the categories into individual columns for the categories. So that this big list of purchases I have is sorted out into their own column that matches the category and then sums it too. Does this make sense? I was sort of avoiding any VBA for this but if thats how it has to be done then thats cool.
     
  2. Gregg2, Dec 7, 2010
    Last edited: Dec 7, 2010

    Gregg2 macrumors 603

    Joined:
    May 22, 2008
    Location:
    Milwaukee, WI
    #2
    No. But, maybe it's just too early in the morning. :confused:

    Ah, I think GGJ nailed it down there. (double entendre intended)
    You must be in the Eastern Time Zone too. ;)
     
  3. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
  4. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #4
    Yes, but this is what I currently have. I guess what I needed to note further was that I would like to remove the 0's or Blanks. Removing the blanks will remove the need to have each category column be as long as my un-sorted category column.
     
  5. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #5
    If I understand you have the original set up in the first post. Why not use Pivot Table, and you can do that with just a click? You can also setup whether 0 or blanks are a problem.

    But then again, I may have misunderstood what you want.
     
  6. GGJstudios, Dec 7, 2010
    Last edited: Dec 7, 2010

    GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #6
    You can remove the zeros by adding two successive double quote marks after the last comma in the formula. However, you can't remove the blank cells.
    ScreenCap 2.PNG
     
  7. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #7
    I know, I think I am just explaining this very poorly. But the idea is to have no blanks or 0's at all, rather if my un-sorted column is 15 rows long and contains only 2 references to "Gas" that my "Gas" column will only be 2 rows long with those two references.
     
  8. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #8
    No, you're explaining it well. I understand what you want. You simply can't do that with Excel. You can hide entire rows or columns, but you can't hide individual cells. There's simply no way to automatically do what you want.

    Now, after you've entered all your data and have all the information there, you can change the formulas to values and sort each column to eliminate the blank/zero cells. It's not automated, but it would give you the end result you seek.
     
  9. emw macrumors G4

    emw

    Joined:
    Aug 2, 2004
    #9
    A pivot table isn't a bad idea, but you may be just as well off sorting by category then using subtotal. I'm on my iPhone so can't give you an Excel screen grab, but it would end up something like:


    Cat Price

    Gas $50
    Gas $25
    Gas $30
    ... $105
    Food $80
    Food $74
    Food $22
    .... $176


    And so forth where "..." would be the subtotal field label.
     
  10. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #10
    Ok, this is the answer I was looking for. At least its absolute.

    So I looked into the pivot tables and I think you guys have a good suggestion here. So far it looks like a clean alternative to what my original goal was.
    Cheers!
     

Share This Page