+ Reply to Thread
Results 1 to 6 of 6

Spend Analysis Categories

  1. #1
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Spend Analysis Categories

    Hi all,

    I'm working on some spending analysis and I'm struggling with the categorisation of transactions.
    I've made a small sample example attached below. I've listed all the suppliers I expect to see transactions from in the 'Ref' sheet, and give each supplier a corresponding category from a finite drop down list.
    image.png
    In my 'Banking' sheet, I have complex descriptions in column B and I want a formula for cells in column D (Category) to tell me which category each transaction falls under based on key words/characters from the transaction description. To make this extra robust, i've thrown in some suppliers with concatenated endings so I assume wildcards (*) will be more appropriate than simply reading the entire string of supplier names.

    Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by aurelien_21; 07-10-2020 at 10:44 AM.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Spend Analysis Categories

    why make things difficult when you could just use one sheet and categorize the items as you enter your banking data ?????

  3. #3
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59
    Hi, the reason is that I import my banking data 300 rows at a time, and at the moment I have over 3000 rows of transactions. As these are made up of around 50 suppliers, inputting categories 3000 times would be inefficient ..
    Last edited by AliGW; 07-10-2020 at 09:33 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Spend Analysis Categories

    In D2 copied down:

    =IFERROR(LOOKUP(1000,SEARCH(Ref!$A$2:$A$5,B2),Ref!$B$2:$B$5),"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    59

    Re: Spend Analysis Categories

    Excellent, THANK YOU !

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Spend Analysis Categories

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 05-04-2020, 06:33 AM
  2. Data Validation from another Workbook (categories and sub categories)
    By pingoui in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-07-2019, 04:59 AM
  3. Trend Analysis/Count different categories
    By ap1591 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2018, 09:55 PM
  4. Spend Analysis
    By spearse in forum Excel General
    Replies: 1
    Last Post: 04-07-2015, 02:43 PM
  5. Replies: 1
    Last Post: 05-15-2013, 06:55 AM
  6. What-if Analysis for multiple months Fuel Spend
    By Ivanur55 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-02-2013, 12:26 PM
  7. How do I estimate the year spend if spend is $26000 as of May?
    By Felicia Pickett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2006, 11:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1