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.

Originally Posted by Fotis1991

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

Originally Posted by TheExcelphile

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.

Originally Posted by Fotis1991

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

Yes, It worked fine.
Thanks for support.

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

Originally Posted by Ghozi Alkatiri

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

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

Originally Posted by udontnome99

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!

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!

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.