Excel Creating Groups by Macro

Panoos64 · Mar 3, 2014

Hi to everyone,
I am a new user into excel using macros and would appreciate to advice me, how I can automate a worksheet, after I have about 300 rows as expenses of which I want some of these to distinguish groups. Note that the name of expense is not stable for each time which i download the file, so based on the name of the output, i would like to write a macro so that running this macro to enter a number in column B for each expense which i want to group it so that then using filter i can issue any group. Note after filtering i would like to have the total in column C. Please see below extract of spreadsheet. Any help will be great appreciating. Best regards

<colgroup><col style="mso-width-source:userset;mso-width-alt:15762;width:323pt" width="431"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> </colgroup><tbody>
[TD="class: xl66, width: 624, colspan: 3"] Monthly expenses
[/TD]

[TD="class: xl64"] Expense
[/TD]
[TD="class: xl64"] Group [/TD]
[TD="class: xl65"] Amount [/TD]

[TD="class: xl63"]EXP HOUSEK/CLEANING SUPPLIES[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]255[/TD]

[TD="class: xl63"]EXP HOUSEK/SHAMPOO/SOAP/BATH GELS[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]112[/TD]

[TD="class: xl63"]EXP HOUSEK/CLEANING EXPENSES[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]300[/TD]

[TD="class: xl63"]EXP HOUSEK/LAUNDRY & DRY CLEAN[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1456[/TD]

[TD="class: xl63"]EXP/HOUSEK/STAFF MEALS[/TD]
[TD="class: xl67"]3[/TD]
[TD="class: xl67"]150[/TD]

[TD="class: xl63"]EXP HOUSEK/LINEN REPLACEMENTS[/TD]
[TD="class: xl67"]5[/TD]
[TD="class: xl67"]245[/TD]

[TD="class: xl63"]EXP HOUSEK/STAFF UNIFORMS[/TD]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]345[/TD]

[TD="class: xl63"]EXP HOUSEK/CARPETS & CURTAINS REPLA[/TD]
[TD="class: xl67"]5[/TD]
[TD="class: xl67"]678[/TD]

[TD="class: xl63"]EXP HOUSEK/GUESTS COMPENSATIONS[/TD]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]456[/TD]

[TD="class: xl63"]EXP HOUSEK/DISCOUNT ALLOWANCES[/TD]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]348[/TD]

[TD="class: xl63"]EXP REST/KITCH/CLEANING SUPPLIES[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]478[/TD]

[TD="class: xl63"]EXP/REST/KITCH.UTENCILS[/TD]
[TD="class: xl67"]5[/TD]
[TD="class: xl67"]865[/TD]

</tbody>

hippiehacker · Mar 4, 2014

a vba is much more complicated to extend if required but its your choice

Code:

Sub panoos64()
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
If UCase(Cells(i, 1).Value) = "EXP HOUSEK/CLEANING SUPPLIES" Or _
 UCase(Cells(i, 1).Value) = "EXP HOUSEK/SHAMPOO/SOAP/BATH GELS" Or _
  UCase(Cells(i, 1).Value) = "EXP HOUSEK/CLEANING EXPENSES" Or _
   UCase(Cells(i, 1).Value) = "EXP HOUSEK/LAUNDRY & DRY CLEAN" Or _
    UCase(Cells(i, 1).Value) = "EXP REST/KITCH/CLEANING SUPPLIES" Then
     Cells(i, 2) = 1
End If
If UCase(Cells(i, 1).Value) = "EXP HOUSEK/STAFF UNIFORMS" Then Cells(i, 2) = 2
If UCase(Cells(i, 1).Value) = "EXP/HOUSEK/STAFF MEALS" Then Cells(i, 2) = 3
If UCase(Cells(i, 1).Value) = "EXP HOUSEK/LINEN REPLACEMENTS" Or _
 UCase(Cells(i, 1).Value) = "EXP HOUSEK/CARPETS & CURTAINS REPLA" Or _
  UCase(Cells(i, 1).Value) = "EXP/REST/KITCH.UTENCILS" Then
     Cells(i, 2) = 5
End If
If UCase(Cells(i, 1).Value) = "EXP HOUSEK/GUESTS COMPENSATIONS" Or _
 UCase(Cells(i, 1).Value) = "EXP HOUSEK/DISCOUNT ALLOWANCES" Then
     Cells(i, 2) = 6
End If
Next i
End Sub

Panoos64 · Mar 4, 2014

Ok I will try it and i will inform you accordingly. However many thanks for your prompt respond and your help to this subject. Best regards

Panoos64 · Mar 7, 2014

I am sorry for my delay answer. Just today i created the macro. It works perfect. Thanks once again. I appreciated.

Excel Creating Groups by Macro

Panoos64

Well-known Member

hippiehacker

Well-known Member

Excel Facts

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Excel Creating Groups by Macro

Panoos64

Well-known Member

hippiehacker

Well-known Member

Excel Facts

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock