Assign categories

[jhonydip]

Hi,

This might be a very basic question. I am creating a file for expenditure tracking. My input data source is the monthly activity excel available from financial account. The source file contains date, transaction details, and amount. in order to track (through pivot table) the different area expenditure, I need to assign simple categories (like groceries, restaurant, rent, etc) to the each transactions. How do I do that?
PS - The transaction details is varies in nature, for e.g. it has Walmart, local grocers, Costco, etc. that all can be categorized as "Grocery".

 

[Hui]

Jhonydip

Firstly, Welcome to the Chandoo.org Forums

You have two choices

1. Manually assign categories
2. Use a Formula

1. Manually assign categories
You can manually assign categories and use some tools to help you
If you first convert your data to a Table, you can filter it by different fields
Then assign Categories to all the shown records, then repeat for different values in the fields until all entries have categories

You can select Multiple entries and use Ctrl+Enter to enter a single values into multiple cells

You could also predefine the Categories and then use a Data Validation drop-down to only allow you to choose the predefined categories

2. Use a Formula
If there is enough data in the download you could assign a formula to assist you
eg: where ever Costco appears assign Groceries

The approach will depend on how many entries you are processing every month
If there are only a few < 30-50 do it manually
If there are hundreds look at a Formula

In either case, if you upload a sample file and answer the question about volume of transactions we can give a more targetted response

 

[jhonydip]

Hi Hui...

Thanks for your advice. I have about 1500 entries...hence I was planning to use formula.

Can you help me with formula? What kind of formula should I use?

Also, I have uploaded a sample file.

 

[Hui]

C2: =INDEX(CatList,MATCH(IFERROR(INDEX(NameList,SMALL(IF(ISNUMBER(FIND(NameList,$B2,1)),ROW(NameList)-2),COLUMNS($D$2:D2))),"-"),NameList,0))
Then copy C2 down

That formula uses two named Formula for two lists
NameList: =Sheet1!$F$2:INDEX(Sheet1!$F:$F,COUNTA(Sheet1!$F:$F))
CatList: =Sheet1!$G$2:INDEX(Sheet1!$G:$G,COUNTA(Sheet1!$G:$G))

These are lists of Keywords and Categories


You can add as many Names and categories to the list that you want and the names automagically add the new entries to the lists
They don't need to be in any order
There can be multiple Names Entries for a Category

See attached file:

Enjoy

 

[jhonydip]

That works...thanks a lot Hui...
You guys are awesome.

 

[Hui]

If you wonder how the formula works, it is an Index/Match wrapped around a lookup which is discussed here:
http://chandoo.org/wp/2013/09/12/formula-forensics-no-34-find-a-list-of-words-in-a-cell/