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

Monthly expenses
Expense	Group	Amount
EXP HOUSEK/CLEANING SUPPLIES	1	255
EXP HOUSEK/SHAMPOO/SOAP/BATH GELS	1	112
EXP HOUSEK/CLEANING EXPENSES	1	300
EXP HOUSEK/LAUNDRY & DRY CLEAN	1	1456
EXP/HOUSEK/STAFF MEALS	3	150
EXP HOUSEK/LINEN REPLACEMENTS	5	245
EXP HOUSEK/STAFF UNIFORMS	2	345
EXP HOUSEK/CARPETS & CURTAINS REPLA	5	678
EXP HOUSEK/GUESTS COMPENSATIONS	6	456
EXP HOUSEK/DISCOUNT ALLOWANCES	6	348
EXP REST/KITCH/CLEANING SUPPLIES	1	478
EXP/REST/KITCH.UTENCILS	5	865

hippiehacker · Mar 3, 2014

and what are the criterias for the groups?

mohan.pandey87 · Mar 3, 2014

Output will be somthing like this:

Group	Total_Amount
1	2601
2	345
3	150
5	1788
6	804

If the output like this then you can use pivot table for this.

Panoos64 · Mar 3, 2014

The criteria of the group should be as follow:
Search through column A and where text is EXP HOUSEK/CLEANING SUPPLIES or EXP HOUSEK/SHAMPOO/SOAP/BATH GELS or EXP HOUSEK/CLEANING EXPENSES or EXP HOUSEK/LAUNDRY & DRY CLEAN should be enter the code 1 in column B. Where in column A is text EXP HOUSEK/GUESTS COMPENSATIONS or EXP HOUSEK/DISCOUNT ALLOWANCES should be enter the code 6 in column B and e.t.c.
Thanks a lot for your prompt respond

Panoos64 · Mar 3, 2014

No i would like also to appear expense's description,but note that there is no any group code as 1, 2,3,5,6. These codes i have to automate and should be enter in column B base on expense description using a macro command.
Thank you for your prompt respond.

hippiehacker · Mar 3, 2014

does it needs to be a vba you could also use your table above on a hidden sheet and use vlookup formula to get the group id number

Panoos64 · Mar 3, 2014

Isn't complicate? Please explain me how to use Vlookup formula. I will try it. Thanks

hippiehacker · Mar 3, 2014

use a blank or new worksheet to put your data table from above in it with expense col A and group in Col B (no amount column)

in your actual worksheet you can use the following vlookup formula to get the group ID

=VLOOKUP(A1;Sheet2!A:B;2;FALSE)

Panoos64 · Mar 3, 2014

Ok Thanks. I will try by this way. Best regards

Panoos64 · Mar 4, 2014

No is so difficult by this way. Any other idea? Thanks

Excel Creating Groups by Macro

Panoos64

Well-known Member

Excel Facts

hippiehacker

Well-known Member

mohan.pandey87

Board Regular

Panoos64

Well-known Member

Panoos64

Well-known Member

hippiehacker

Well-known Member

Panoos64

Well-known Member

hippiehacker

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Excel Creating Groups by Macro

Well-known Member

Excel Facts

Well-known Member

Board Regular

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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