Excel - create counts of cells with a particular tag

I have开发者_JS百科 a datasource with 1000 rows containing the following headers:

Date | Label | Amount

I want to be able to loop through each row and determine which category the row belongs in, as defined by the label.

E.g.

1. 1st May | Starbucks | $10.00
2. 1st May | BestBuy | $2.00
3. 2nd May | CostCo | $25.00

In this scenario, row 1 & 3 would be categorised as "Coffee" leading to a total of $35.00.

What would such a macro look like?

Once you've created your separate table of Labels and Categories, you'll need to create a fourth column for 'Category' in your original table. Enter this formula for the first record in that column:

=VLOOKUP(B2,<<label/category table address goes here, e.g. Categories!$A$1:$B$10>>,2,false)

Fill this down your table.

Now, if you want subtotals of the amount by category, you can get this easily with a pivot table.

EDIT:

If you want partial value matching like you described in the comments below, you can use some string manipulation before passing the Label to the VLOOKUP function:

=VLOOKUP(IF(MID(B2,LEN(B2)-1,1)=" ",LEFT(B2,LEN(B2)-2),B2),Categories!$A$1:$B$10,2,FALSE)

This will check to see if there's a space and letter at the end of the Label. If so, it will leave off the last two characters when searching. Otherwise it will just search for the Label. Please note that I have a generic lookup table address in the formula that you will need to change to match your workbook.

I have some idea that can help you, by time reason I can't create right now a macro with the implementation of all my ideas:

• add a columns with this formula

  =FIND(INDIRECT(ADDRESS(initial_row_of_your_match_list + counter; _column_of_your_match_list;1));cell_where_appears_StarbucksA_StarbucksB_CostCo_etc)

The output, in this example given a counter that address gives "starbuck", will be 1 or a number (parcial match) or #VALUE! (no match), the actual macro would be:

* while you loop the counter throu the list
* if the result value is '1' or a number copy the corresponding value of _
  the checking_list (Starbuck) to a Results_column.

finaly you can categorize you rows with vlookup and sumif the cells to obtain the final results.