Sort cell values into categories
Here are the prerequisites. Column A contains dates, column B are categories and column C are values.
The goal with this article is to sort the data into categories (Asia, Europe and USA). They will also be sorted by date. See picture below.
Formula in E3:E16: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1,1,"")), SMALL(IF(($B$2:$B$15=E$1),$A$2:$A$15,""),ROW()-2),"") + Ctrl + Shift + Enter
Formula in F3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1, 1, "")), INDEX(C1:C15, SMALL(IF((E3=$A$2:$A$15)*(E$1=$B$2:$B$15), ROW($B$2:$B$15), ""), 1)), "") + Ctrl + Shift + Enter. Copy this formula down to F16.
Remaining formulas can be found in the excel sample file. Sort-cells-values-into-categories.xls
Download excel sample file for this article.
sort-cells-values-into-categories.xls
(Excel 97-2003 Workbook *.xls)
Download excel sample file for this article using text instead of dates.
Sort-cells-values-into-categories2.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
INDIRECT(ref_text,[a1])
Returns the reference specified by a text string
ROW(reference) returns the rownumber of a reference
COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
SMALL(array,k) returns the k-th smallest row number in this data set.
Related posts:
April 4th, 2009 at 5:12 am
[...] About Me Sort cell values into categories, part 2 Filed in Excel on Apr.04, 2009. Email This article to a Friend In this article I am going to enhance a previous article “Sort cell values into categories“. [...]
September 6th, 2009 at 9:39 pm
Probably it was better to post my comment here not in 'Sort cell values into categories, part 2'. Sorry for that...
If there is text (not number) in column 'Date' - the formula doesn't work. I try to modify the formula, but with no success. Can you help me?
September 7th, 2009 at 9:40 pm
I have attached an excel file to this blog article using text not dates.
September 17th, 2009 at 3:30 pm
Thank you very much! http://www.get-digital-help.com is my favorite site for excel solution.
July 13th, 2010 at 5:11 pm
is there a way to to sort into categories using text that does not use the function 'rows'? I am using a program called xcelsius and rows is not a supported function
July 15th, 2010 at 2:04 pm
As far as I know, row, column, rows and columns are the only functions available to create an array.
You could type {1, 2, 3..} and so on to create an array. Replace row() with {1, 2, 3, ...whatever array you like}