Hi I have a bank statement which has come from online banking and i'm trying to categorize everything. For example i have fuel from a number of different suppliers on my statement but i want the category to be fuel. So i have an if statement to look up the string but this has to be done in seperate columns, i want it to search based on a table of a number of different possible strings and apply a category. Can anyone help?
Hi Mikeburbs,
Can you post a sample pic or file, so that it would be easy to help
Thanks,
aganesan99
On a separate sheet, create a table with two columns listing the vendor and category
On your primary sheet use vlookup in your category cell to determine the category from the table based upon the vendor name.
Look at this link on vlookup
http://www.techonthenet.com/excel/formulas/vlookup.php
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
sounds good - i'll give it a go and let you know - thanks!
I couldn't work the VLOOKUP
This is an example of what i am trying to do
See attached.
Notes: Case must be the same. Cannot have leading or trailing spaces.
The matches need to be exact.
Thanks for this, but it's not quite what i was after, I want it to search the certain string within the cell, so for example instead of having to put in each type of 'Esso' entry eg 'Esso Sheffield', 'Esso London' i want it to search on Esso. Is this possible?
Thanks
This is referred to as a fuzzy search. I don't know of any way to do it with a vlookup. I am giving you a link to fuzzy searches. You may be able to adapt something from this. I personally have not had a need for this and have not done any work in fuzzy searches. It will require some VBA but should be all laid out for you in this link.
http://www.mrexcel.com/forum/excel-q...planation.html
Many thanks. I will look that up.
=SUMPRODUCT(SUMIF(B2:B18,"*"&L3:L5&"*",C2:C18)) would sum for all keywords in range l3:l5
Last edited by martindwilson; 11-18-2013 at 04:53 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks