Categorizing

[Ming-Tang Chen]

Hi, I am new to this forum. And of course, I have a question that I do not know of a good answer for. I have a set of financial data. Each entry has a tag, which is something like "Shopping", "Clothing", "Restaurant", "Groceries", or "Hotel", etc. For all the data, there are about 30, or 40 of such items. I want to consolidate these items into a fewer but broader category of 7 to 8 items. For example, "Shopping" and "Clothing" belong to "Merchandise." "Restaurant" and "Bars" can be in the same "Restaurant". In other words, I want to reduce the number of the category items. I know I can set up the following formula in the new column:

IF(OR(F2="Shopping",F2="Clothing"),"ME",IF(OR(F2="Restaurants",F2="Alcohol & Bars"),"R",IF(F2="Groceries","NE",IF(F2="Rental Car & Taxi","TP",IF(F2="Groceries","NE",IF(F2="Hotel","TP",""))))))

But this way seems so clumsy and prone to errors. Is there a more efficient way of coding to achieve my goal?

Much appreciated,

MT

[#2]

Hi Ming-Tang

Without seeing your spreadsheet, I'll make a general assumption. Have you tried something simple like filtering your column for the current tags and then changing those cells to the new tag?

E.g. Filter for "Shopping" and "Clothing". All cells that have that tag will show in the filtered results. Change the cells with the tab to "Merchandise" (autofill if the cells are all next to one another).

Just a thought?

Cheers
Damien