• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Group data according to category

M

mhghg

Member
Hello ,
I wonder if we have the way to use formula group the data in category column to another column for reports. For example if the activities in Category are relating to Sport, we group them as Sport in the Group column. If activities are related to music, they will be grouped as music
Oops I cannot upload my excel !!!
 
Hi ,

If you cannot upload a .xlsx / .xlsm / .xls / .xlsb file on this forum , just rename it to .docx and upload it. Who ever downloads it will rename it and work on it. Please mention the file extension in your post when you upload your file.

Narayan
 
Hi ,

I have no idea what the problem is ; however , given that you are facing a problem , can you use any public file-sharing website such as GoogleDocs , DropBox , SkyDrive , and upload your file to any of those websites ?

Once you have uploaded your file to such a website , give others permission to download and open your file , and then post the share link in this thread.

Narayan
 
NARAYANK991 said:
Hi ,

I have no idea what the problem is ; however , given that you are facing a problem , can you use any public file-sharing website such as GoogleDocs , DropBox , SkyDrive , and upload your file to any of those websites ?

Once you have uploaded your file to such a website , give others permission to download and open your file , and then post the share link in this thread.

Narayan
Click to expand...
 
Thank you Khalid, but is there any other way instead of creating the helper column as the data table is updated with additional categories and activities every year
 
Hi:

I am afraid the answer is no, you need to have keywords and corresponding categories to get this formula right. Excel would not know which activity should be categorised as which category group unless you tell it to do it. And I believe that can be done only by maintaining a separate table of keywords and activities .

Thanks
 
Hi:

SEARCH searches for the values in your category cells in the range $B$2:$B$44. It returns the position number of the start of that value if found, for e.g. it will search for “Activities” from the keywords and find the position of “Activities” in the cell B5” Extra School Activities”.

The largest number of characters allowed in a cell is 32767 or 2^15-1,
having a lookup value of 2^15 therefore guarantees that it will search for the entire text in a particular cell and will return the starting position of the search word if found.

Thanks
 
Thanks, Nebu. I tried to use the index & match but it does not return the value for some categories. Do you know why?
 
Hi:

Index match won't work for you because it searches for the exact match .In your case you will have to search for a part of a string .

Thanks
 
In my data sheet table tblSport, if I add a few more rows of data, how come they do not reflect in my calculation sheet, which I am using {tblsport} CSE to copy data across.
 
Hi:

I would suggest you to start this as a new thread so that it will be answered by a wider audience.

Thanks
 
You must log in or register to reply here.
Back
Top