Closed Thread
Results 1 to 15 of 15

How to categorize a big list of data in MS Excel 2010?

  1. #1
    Registered User
    Join Date
    11-21-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    30

    How to categorize a big list of data in MS Excel 2010?

    Hi,

    Suppose I have 1000 data in column A and I want to categorize the column A data in column B. These 1000 data I want make into 4 categories in column B. So what I was thinking if I could have write single formula in column B and drag till the end, the each data cell of column A will fall respective category in column B. Please find the attachment to understand better.
    In this sheet I have two columns Keyword and Category.
    Condition:
    If any cell containing "paper" or "question paper" or "sample papers" will come under category called Sample Paper in column B.
    If any cell containing "syllabus" will come under category called Syllabus in column B.
    If any cell containing "book" or "books" will come under category called Book in column B
    I can solve this by using Filter in Column A but I want a formula which can be written in column B and drag till the end to get result.

    Any suggestion or help highly appreciated.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to categorize a big list of data in MS Excel 2010?

    Why "icse board question answer", is in the Category >> Sample Paper?

    How many categories of keywords are you using?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    11-05-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: How to categorize a big list of data in MS Excel 2010?

    Hi kalucharan!

    I've solved your problem for you.

    Don't forget to click on the little star to the left of this post if you feel I helped!
    Attached Files Attached Files
    Taming the Excel dragon... www.TheExcelphile.com

  4. #4
    Registered User
    Join Date
    11-21-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to categorize a big list of data in MS Excel 2010?

    Quote Originally Posted by Fotis1991 View Post
    Why "icse board question answer", is in the Category >> Sample Paper?

    How many categories of keywords are you using?
    Hey, Thanks a lot for replying. I am very sorry, I have missed out one more condition.
    If any cell containing question or questions will come under Sample Paper.

    I guess now it is clear.

    Thanks

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to categorize a big list of data in MS Excel 2010?

    So, TheExcelphile's suggestion must work for you. Doesn't it?

  6. #6
    Registered User
    Join Date
    11-21-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to categorize a big list of data in MS Excel 2010?

    Quote Originally Posted by TheExcelphile View Post
    Hi kalucharan!

    I've solved your problem for you.

    Don't forget to click on the little star to the left of this post if you feel I helped!
    Hi TheExcelphile,

    Thanks a lot TheExcelphile. You really saved lot of time of mine. Once again thank you so much for taking your valuable time to solve this.

  7. #7
    Registered User
    Join Date
    11-21-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to categorize a big list of data in MS Excel 2010?

    Quote Originally Posted by Fotis1991 View Post
    So, TheExcelphile's suggestion must work for you. Doesn't it?
    Yes, It worked fine.
    Thanks for support.

  8. #8
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    552

    Re: How to categorize a big list of data in MS Excel 2010?

    write formula at B3
    =INDEX({"Sample Paper";"Syllabus";"Book"};IF(ISNUMBER(FIND("paper";A3));1;IF(ISNUMBER(FIND("question";A3));1;IF(ISNUMBER(FIND("sample";A3));1;IF(ISNUMBER(FIND("syllabus";A3));2;IF(ISNUMBER(FIND("book";A3));3))))))

    copy down
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-21-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to categorize a big list of data in MS Excel 2010?

    Quote Originally Posted by Ghozi Alkatiri View Post
    write formula at B3
    =INDEX({"Sample Paper";"Syllabus";"Book"};IF(ISNUMBER(FIND("paper";A3));1;IF(ISNUMBER(FIND("question";A3));1;IF(ISNUMBER(FIND("sample";A3));1;IF(ISNUMBER(FIND("syllabus";A3));2;IF(ISNUMBER(FIND("book";A3));3))))))

    copy down
    Hi Ghozi,
    Thanks a lot, You really simplified the formula.

    Regards
    Kalucharan

  10. #10
    Registered User
    Join Date
    09-15-2014
    Location
    Nashville, Tennessee
    MS-Off Ver
    2010
    Posts
    1

    Re: How to categorize a big list of data in MS Excel 2010?

    Hi everyone I'm new. I have a column of numbers I need to sort. All numbers are in column A. I need the results to be in column B.
    if A1 is less than 707 it's below basic. between 707 and 751 basic. between 752 and 801 proficient. Finally larger than 801 would be advanced. I keep trying to write this as an IF= formula but it keeps coming back wrong

    so far I have

    =IF(A2<=707,"Below Basic",IF(A2>707<752,"Basic",IF(A2>752<802,"Proficient",IF(A2>=802,"Advanced"))))

    any help would be awesome

  11. #11
    Registered User
    Join Date
    11-17-2014
    Location
    Canada
    MS-Off Ver
    Excel 10
    Posts
    1

    Re: How to categorize a big list of data in MS Excel 2010?

    Quote Originally Posted by udontnome99 View Post
    Hi everyone I'm new. I have a column of numbers I need to sort. All numbers are in column A. I need the results to be in column B.
    if A1 is less than 707 it's below basic. between 707 and 751 basic. between 752 and 801 proficient. Finally larger than 801 would be advanced. I keep trying to write this as an IF= formula but it keeps coming back wrong

    so far I have

    =IF(A2<=707,"Below Basic",IF(A2>707<752,"Basic",IF(A2>752<802,"Proficient",IF(A2>=802,"Advanced"))))

    any help would be awesome
    i am looking for something similar, can any person help, please?? I have a dataset with a column of numbers and would like to categorize them into different categories based on value, since it is dynamic dataset, any help would be great! Thank you!

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: How to categorize a big list of data in MS Excel 2010?

    The proper way to write the formula is

    =IF(A2<=707,"Below Basic",IF(A2<752,"Basic",IF(A2 <802,"Proficient",IF(A2>=802,"Advanced",""))))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  13. #13
    Registered User
    Join Date
    11-05-2016
    Location
    PHILIPPINES
    MS-Off Ver
    2010
    Posts
    1

    Re: How to categorize a big list of data in MS Excel 2010?

    I want to make a big list of data.
    In first column its DATE den in second is Branch (it contains 30 branches more or less)
    under branch is Beginning Balance, Payment, Delivery and End Balance.
    I just want to use a something like dropdown for not too long to scroll.
    I hope you can help me in my problem. thankyou!

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,831

    Re: How to categorize a big list of data in MS Excel 2010?

    This is an old post which you have "hijacked" so please start a NEW thread.

    Add a sample file (not image) to your thread showing expected results.

    To Attach a File:

    1. Click "Go Advanced"
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,316

    Re: How to categorize a big list of data in MS Excel 2010?

    brianemae90 welcome to the forum

    As mentioned, unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

Closed Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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