Categorize credit card transaction details by predefined buckets (e.g. gas, food, entertainment, etc.)

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
106
Hi folks,

I want to do a pivot table based on credit card transaction data that is categorized - obviously the raw transaction data won't make for a very good pivot table since it's not categorized... When downloading a credit card stmt in csv I get transaction data something like below (blank fields not relevant):


ABCDE
Card #Trans. Date #Posting DateAmountDescription
100ABC Car dealership
25#123 Woolmart
50XYZ Gas Mart
125ABC Restaurant
75ZYZ Liquor Store
100Costking #1234
40Lot ABC Parking
15Mcdavid's #56568
80ABC Auto Park

<tbody>
</tbody>

I want a lookup formula that allows me to put everything into say 1 of 5 predefined buckets (i.e. fast food, auto, entertainment, department stores, other) so I can then do a pivot table that's clear.

"other" would just be a catch all for things that didn't match anything in the master table.

So based on my predefined master table, if the transaction description field contained the word "Costking", it would go under "department stores". And "ABC Car dealership" would fall under "Auto" (since my master table would have Auto next to Costking.

Any ideas?

Thanks
James
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,099,507
Messages
5,469,029
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top