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 file
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">
Welcome! I am 
){kind=link}
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