+ Reply to Thread
Results 1 to 10 of 10

How to extract info based on any defined category

  1. #1
    Registered User
    Join Date
    11-15-2011
    Location
    India
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    40

    Unhappy How to extract info based on any defined category

    Dear Techies,

    I am a newbie to excel so please excuse for my foolishness. I have an excel file in which i store my daily accounts (just income and expense) of very small business. Now i want to categorize and see the expenses by the end of the month or week (as i wish). for eg; the content of my excel sheet are;

    Opening Balance 1000

    Date; Name-of-Payee; Payment-Details; Received; Payment; Balance;
    1/11/11; Cremo; Petrol ; ; ; 100; 900;
    1/11/11; Cremo; Mobile Bill; ; 300; 600;
    2/11/11; ; Sale of Goods; 150; ; 750;
    2/11/11; Cremo; Carpenter; ; 200; 550;
    3/11/11; ; Sale of Goods; 100 ; ; 650;
    4/11/11; Cremo ; Petrol; ; 100; 550;
    5/11/11; Cremo; Internet Bill; ; 200; 350;
    6/11/11; ; Sale of Goods; 500; ; 850;
    7/11/11; Cremo; Raw materials; ; 350; 500;
    8/11/11; ; Sale of Goods; 200; ; 700;
    9/11/11; Cremo; Raw materials; ; 200; 500;
    10/11/11; Cremo; Petrol; ; 150; 350;


    now i have to see in the end like;
    Petrol; 350;
    Raw Materials; 550;
    Communication; 500;
    Total Sale; ; 950;

    etc.


    how can i extract this info from the above text which i already entered to my excel file....

    PLEASE HELP..... I am searching for a way around for this for the last entire week...

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How to extract info based on any defined category

    There's no correlation between "Communication" and "Mobile Bill" "Internet Bill" so these wont be classed as Communication

    Take a look at Pivot Tables but this may be a little daunting at this stage.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: How to extract info based on any defined category

    Give it a shot!!!

    creamecake example.xlsx
    Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  4. #4
    Registered User
    Join Date
    11-15-2011
    Location
    India
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    40

    Re: How to extract info based on any defined category

    I also get in to Pivot tables. But i am not able to understand how to use it in my scenario. Regarding the Relation of Comminication, Internet Bill and Moile Bill we can add a category column and mention a code for each category and mention the code when we are adding the entry, for eg;

    Date; Name-of-Payee; Payment-Details; Received; Payment; Balance; Category-Code
    1/11/11; Cremo; Mobile Bill; ; 300; 600;101
    5/11/11; Cremo; Internet Bill; ; 200; 350;101

    here the category code "101" is identical for Internet Bill and Mobile Bill...

    But i dont know where or how to define these codes in excel and how can i manipulate it to get my result...

    PLS ADVICE...

  5. #5
    Registered User
    Join Date
    11-15-2011
    Location
    India
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    40

    Re: How to extract info based on any defined category

    Quote Originally Posted by khamilton View Post
    Give it a shot!!!

    Attachment 129000

    Yes it is working! But i cant understand how can i use it... i mean the formula which u used in it...

    my excel sheet will have around 150 entries... so where can i place this, how to put the formula, i am lil confused...

    But i love to see it is working...

    Thx a lot khamilton....

    Could you pls explain it to me... or if you have any easy method by categorizing, then pls advice...

  6. #6
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: How to extract info based on any defined category

    Its like this =sumif(range,criteria,sum range) range---so you have your range that you want your criteria to match then your sum range will only sum the number in the rows that meet your criteria.HTH

  7. #7
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: How to extract info based on any defined category

    Maybe this will help you a little more.

    creamecake example.xlsx

  8. #8
    Registered User
    Join Date
    11-15-2011
    Location
    India
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    40

    Re: How to extract info based on any defined category

    Quote Originally Posted by khamilton View Post
    Maybe this will help you a little more.

    Attachment 129002
    This one is really better dear...

    Now one more thing. Could u pls advice, how can i simply edit this fomula, when i am applying in my monthly account sheet, where the number of entries will be varying in between 100 to 200 lines.

    Is it like editing the complete $C$3:$C$end of the coloumn and the same way to $E and $D

    =IF(SUMIF($C$3:$C$14,I3,$E$3:$E$14)=0,SUMIF($C$3:$C$14,I3,$D$3:$D$14),SUMIF($C$3:$C$14,I3,$E$3:$E$14))

    or it there any simplest method?

    Once again thanking you dear for your GREAT solution.

  9. #9
    Registered User
    Join Date
    11-15-2011
    Location
    India
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    40

    Re: How to extract info based on any defined category

    Quote Originally Posted by khamilton View Post
    Maybe this will help you a little more.

    Attachment 129002

    Dear Khamilton....

    I feel so happy after applying your formula in my sheet.... REALLLY REALLLY REALLLYYYY BIIIIIGGGGGGGGGGGGGGGG THHHHHHHAAAAAAAAAANNNNKKKKSSSSS DEARRRR

    THANK YOU VERY MUCH!!!!

    In my actual excel sheet, i made a section called "Categorized Expenses" and i manually edited the expense categories like "Petrol" Mobile Bill" , etc, then in the next coloumn i copied your formula and edited according to my excel sheets coloumns and rows...

    the result was AAMMMMAAAAZZZZZIIIIIINNNNNGGGGG Dear...

    now i can see expenses of each category easily....


    After i finished this think, i saw that in your example file, you didnt typed "Petrol" in the "Unique entries from coloumn C" , rather it was also a formula.

    but when i tried to apply that formula, it didnt worked for me... i tried with my actual excel sheet as well as in your example sheet itself, but in both case it is not working... i am attaching that sample which you gave me, with my trying in automated categorization. PLease check and advice how to achieve that too...


    Once again thanking you....
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-15-2011
    Location
    India
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    40

    Re: How to extract info based on any defined category

    Quote Originally Posted by creamecake View Post
    Dear Khamilton....

    I feel so happy after applying your formula in my sheet.... REALLLY REALLLY REALLLYYYY BIIIIIGGGGGGGGGGGGGGGG THHHHHHHAAAAAAAAAANNNNKKKKSSSSS DEARRRR

    THANK YOU VERY MUCH!!!!

    Khamilton...... Awaiting your response....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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