## 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.

### Category: Split values

Split data across multiple sheets in excel (vba)

In this post I am going to show how to create a new sheet for each airplane using vba. The […]Comments(29) Filed in category: Add-in, Excel, Split values

Text to columns: Split words in a cell (excel array formula)

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]Comments(23) Filed in category: Excel, Split values

User defined function to split words in a cell range into a cell each in excel

This post describes how to split words in a cell range into a cell each using a custom function. I […]Comments(13) Filed in category: Excel, Split values

Split values equally into groups using excel array formula

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]Comments(13) Filed in category: Excel, Split values

Categorize values into multiple columns (excel formulas)

In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column […]Comments(6) Filed in category: Excel, Split values

Categorize data entry values (vba)

In a previous post I described how to simplifiy data entry. Now it is time to put values in separate […]Comments(3) Filed in category: Excel, Split values

Excel: Using lookup to categorize numbers

Problem: Values between 0 and 4 returns Small. Values between 5 and 9 returns Medium. Values between 10 and 15 […]Comments(3) Filed in category: Excel, Split values

Categorize values into multiple columns using vba in excel

I am fairly new to vba and I am amazed of how much you can automate in excel. In this […]Comments(1) Filed in category: Excel, Split values

Comments(0) Filed in category: Excel, Split values

### 7 Responses to “Sort cell values into categories”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] 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“. [...]

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?

I have attached an excel file to this blog article using text not dates.

Thank you very much! https://www.get-digital-help.com is my favorite site for excel solution.

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

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}

You have one mistake in your example spreadsheet and your article

For the formula in F3 , you don't want the range for the index

C1:C15 to become C2:C16 in row 2, then C3:C17 in row 3,

It needs to be $C$1:$C$15

I was helping someone who was working through some of his data using your example and fix in his excel and then, saw your example had the same problem.

You have

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.

It should be :

Formula in F3: =IF(ROW()-2<=COUNT(IF($B$2:$B$15=E$1, 1, "")), INDEX($C$1:$C$15, 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.