## 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 (vba).

In this post I´ll show you how to do the same using only excel formulas.

### Create unique distinct column headers

Array formula in B20:

Copy (CTRL + C) cell B20 and paste (Ctrl + V) into cells C20 and D20. See picture below.

### Categorize cell values into each column

Array formula in B21:

Copy (CTRL + C) cell B21 and paste (Ctrl + V) into cells B21:C23 and D21:D24. See picture below.

**Download excel example fil****e**

Categorize data into multiple columns (formulas).xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**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

**ROW(**reference**)** returns the rownumber of a reference

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

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

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

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

### 6 Responses to “Categorize values into multiple columns (excel formulas)”

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

Slightly different topic, but been thinking about this for a long while:

It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum))

But all these are single conditions -- you can't pass multiple conditions, say for example: USA or China or France in column_countries and Airbus or Boeing in column_producer. I know 2 solutions to get around this limitation, but none is perfect:

1) =SUM((column_plane=TRUE)*((column_countries="USA")+(column_countries="China")+(column_countries="France"))*((column_producer="Airbus")+(column_producer="Boeing"))*(column_to_sum)) -- this one works great, but it's not dinamic at all. What if the user chooses 10 multiple countries or more? You're not gonna write tens of equations for each condition.

2) =SUM((column_countries=IF(TRANSPOSE(contries_selected)=TRUE,TRANSPOSE(countries_selected_names)))*(column_producer="Airbus")*(column_to_sum)) -- that works fine, but this is producing a 2-dimentional matrix and hence is good for one condition only -- notice column_producer has only one value. Now, what if you want to pass multiple values to column_producer as well?

In SQL this equates to

SELECT SUM(column_to_sum)

FROM table

WHERE

(country = "USA" OR country = "France" OR country = "China")

AND

(producer ="Boeing" OR producer="Airbus")

Any idea how to replicate that in Excel???

Paul,

Take a look at these posts:

https://www.get-digital-help.com/2009/12/31/sum-adjacent-values-using-multiple-lookup-text-values-in-a-column-in-excel/

and

https://www.get-digital-help.com/2010/01/03/sum-adjacent-values-from-a-range-using-multiple-lookup-values-in-excel/

Thanks for commenting!

/Oscar

Paul, see this post: https://www.get-digital-help.com/2010/03/15/pass-multiple-conditions-dynamically-in-excel/

[...] You can find the question here. [...]

please help,

i have two column, Column A has a team name and column B has people in team, i would like to see each team in its own column..

team 1 person 1 team 1 team 2 team 3 team 4

team 1 person 2 person 1 person 1 person 1 person 1

team 1 person 3 person 2 person 2 person 2 person 2

team 1 person 4 person 3 person 3 person 3 person 3

team 1 person 5 person 4 person 4 person 4 person 4

team 1 person 6 person 5 person 5 person 5 person 5

team 1 person 7 person 6 person 6 person 6 person 6

team 1 person 8 person 7 person 7 person 7 person 7

team 1 person 9 person 8 person 8 person 8 person 8

team 1 person 10 person 9 person 9 person 9 person 9

team 1 person 11 person 10 person 10 person 10 person 10

team 1 person 12 person 11 person 11 person 11 person 11

team 1 person 13 person 12 person 12 person 12

team 1 person 14 person 13 person 13

team 1 person 15 person 14 person 14

team 1 person 16 person 15

team 1 person 17 person 16

team 1 person 18 person 17

team 1 person 19 person 18

team 2 person 1 person 19

team 2 person 2

team 2 person 3

team 2 person 4

team 2 person 5

team 2 person 6

team 2 person 7

team 2 person 8

team 2 person 9

team 2 person 10

team 2 person 11

team 3 person 1

team 3 person 2

team 3 person 3

team 3 person 4

team 3 person 5

team 3 person 6

team 3 person 7

team 3 person 8

team 3 person 9

team 3 person 10

team 3 person 11

team 3 person 12

team 4 person 1

team 4 person 2

team 4 person 3

team 4 person 4

team 4 person 5

team 4 person 6

team 4 person 7

team 4 person 8

team 4 person 9

team 4 person 10

team 4 person 11

team 4 person 12

team 4 person 13

team 4 person 14

Xit,

I moved your comment. The answer is in this post.

See attached file:

Categorize-into-teams.xlsx