You are here:

Excel/Rename/Group Cells and add corresponding Expense amounts

Advertisement


Question
QUESTION: Hello,

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.

My issue:
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,

Monja

ANSWER: Hi Monja,

I am a little uncertain as to exactly what you want.  Could you clarify a bit?

In particular:

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?

Damon

---------- FOLLOW-UP ----------

QUESTION: Hello,

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!

Monja

Answer
Hi Monja,

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:

__________________________________________________

Sub ReclassifyExpenses()
  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"
        Case "Theatre"
         Cells(iRow, "C") = "Entertainment"
        Case "Taxi", "Fuel", "Bus", "Metro"
         Cells(iRow, "C") = "Transportation"
     End Select
     
  Next iRow
  
End Sub
__________________________________________________

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:

  =SUMIF(C2:C37,"Meals",B2:B37)

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:

 =SUMIF(C2:C37,"Entertainment",B2:B37)

and Transportation would be

 =SUMIF(C2:C37,"Transportation",B2:B37)

and similarly for each super-category.

I hope this helps.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.