Excel/Rename/Group Cells and add corresponding Expense amounts
First off, I am working with Excel 2007 on a Microsoft 7 Professional PC. I am proficient with Excel (7/10), and have worked with VBA before, but I would rate my experience as 2/10 in this matter.
The testing spreadsheet I am using has 2 columns: Description of Expense on Column A and Expense Amount in Column B with 37 rows. I am to group the description of expenses into corresponding categories and total their expense amounts.
Next to these two columns I have two rows set up as Recategorized Description and Totals for the new categories..
For example, my testing spreadsheet includes Bistro and Cafeteria, which are to be grouped into one category called "Meals" to then total all the "Meals" related expenses into one.
If further information is necessary, please let me know.
Thank you very much,
ANSWER: Hi Monja,
I am a little uncertain as to exactly what you want. Could you clarify a bit?
1. When you refer to grouping Bistro and Cafeteria into one category, are you wanting a macro that will do this? Or are you doing this manually? If via macro, how would you identify which categories should be grouped?
2. When you refer to grouping categories, are you simply referring to totaling the expenses of all the categories within the group, or are you referring to Excel Data Grouping so that only the rows containing the desired categories are visible?
3. Are you adding a third category column in which you place the categories such as Bistro and Cafeteria? Are you adding a fourth super-category column that lists both the Bistro and Cafeteria items as Meals? If not, where are the category and super-category designations for each item located?
---------- FOLLOW-UP ----------
1. I would like a macro to do this. Essentially I was thinking to add a column and perhaps through an if statement have it reclassify bistro and cafeteria from the old column to "meals" in this new column.
2. Yes, for the reclassified (probably a better word than grouping) categories, those expenses should be totaled. So for all the "meals" entries in the spreadsheet, the corresponding expense amount should be included in the expense total. All rows should remain visible.
3. The column with the "bistro" and "cafeteria" classifications are already there. The first column is the description, under which the bistro and cafeteria would show. The second column is the expense amount. So maybe adding a third "super-category" column would help, correct? There are several categories that would need to be condensed and reclassified, meals being one of them.
This would be a condensed sample sheet of the sheet I'm working with: (The third row is an example of the desired reclassification that the macro should do)
Descr. of Expense | Expense amount (| Reclassification)
Bistro 100 Meals
Cafeteria 200 Meals
Bistro 150 Meals
Theatre 300 Entertainment
Cafeteria 250 Meals
Does that clarify my question?
Thank you very much!
Yes, the clarification really helps.
First, here is a macro you can use to do the reclassifications from the descriptions in column A to the reclassified category in column C:
Dim iRow As Long
'Loop from row 2 to the last data-filled row in column A
For iRow = 2 To Range("A65536").End(xlUp).Row
Select Case Cells(iRow, "A")
Case "Meals", "Bistro", "Cafeteria"
Cells(iRow, "C") = "Meals"
Cells(iRow, "C") = "Entertainment"
Case "Taxi", "Fuel", "Bus", "Metro"
Cells(iRow, "C") = "Transportation"
Note that I added the "super-category" Transportation to reclassify Taxi, Fuel, Bus, and Metro. I did this as an example of how to add additional re-classifications. Simply add an additional Case line and Cells(iRow, "C") = "..." for each super-category.
Now, to sum the values of each super-category simply use a formula like:
This sums all the expenses in the Meals super-category. Just make sure that the range (C2:C37) contains all the super-category range, and B2:B37 the corresponding Expense values.
So the expenses for Meals would be:
and Transportation would be
and similarly for each super-category.
I hope this helps.