## Sort cell values into categories

*Article last updated on July 31, 2017*

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.

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Text to columns: Split words in a cell [Array formula]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Split values equally into groups

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

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 […]

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 […]

Categorize values into multiple columns [VBA]

I am fairly new to vba and I am amazed of how much you can automate in excel. In this […]

This workbook lets you split expenses evenly with other people. Type name, expense and amount in the excel table on […]

Sam asks: S/N RailCorp Ref Number Date In 77203 HRC mod program 10377 24/05/2011 77204 HRC mod program 10285 20/04/2011 […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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