Sorting out columns in Excel

[GimmeSlack12]

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.

 

[Gregg2]

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?

No. But, maybe it's just too early in the morning.

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

 

[GGJstudios]

Do you mean something like this?

​

 

[GimmeSlack12]

Do you mean something like this?

View attachment 263118​

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.

 

[exegete77]

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.

 

[GGJstudios]

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.

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.

​

 

[GimmeSlack12]

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.

View attachment 263199​

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.

 

[GGJstudios]

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.

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.

 

[emw]

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.

 

[GimmeSlack12]

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.

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!