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.
Recommended posts
Lookup two index columns returning multiple matches in excel
Array formula in B14: =INDEX(Product, SMALL(IF(($C$10=ID_num)*(OrderDate=$C$11), ROW(Product)-MIN(ROW(Product))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as necessary. […]
A more userfriendly version of: Automatically filter unique row records from multiple columns
In this article I will try to automatically filter unique row records from multiple columns, without using arrays. To do […]
Lookup multiple values in different columns and return multiple values
Jason C asks: I have a set of data, like the one you used in the original example that also […]
6 Responses to “Categorize values into multiple columns (excel formulas)”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
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.
){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