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:
=INDEX($B$4:$B$13, MATCH(0, COUNTIF($A$20:A20, $B$4:$B$13), 0)) + CTRL + SHIFT + ENTER.
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:
=INDEX($C$4:$C$13, SMALL(IF($B$4:$B$13=B$20, ROW($B$4:$B$13)-MIN(ROW($B$4:$B$13))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER
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.
Related posts:
- Categorize values into multiple columns using vba in excel
- How to return multiple values using vlookup in excel
- Lookup two index columns returning multiple matches in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
- Sum adjacent values using multiple lookup text values in a column in excel
- Lookup two index columns using min max values and a date range as criteria
- Return multiple values if in range in excel
- Filter duplicates within same date, week or month in excel
- Merge two columns with possible blank cells in excel (formula)
- How to filter numbers inside (and outside) number ranges in excel
March 12th, 2010 at 4:49 pm
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???
March 12th, 2010 at 6:34 pm
Paul,
Take a look at these posts:
http://www.get-digital-help.com/2009/12/31/sum-adjacent-values-using-multiple-lookup-text-values-in-a-column-in-excel/
and
http://www.get-digital-help.com/2010/01/03/sum-adjacent-values-from-a-range-using-multiple-lookup-values-in-excel/
Thanks for commenting!
/Oscar
March 15th, 2010 at 11:08 am
Paul, see this post: http://www.get-digital-help.com/2010/03/15/pass-multiple-conditions-dynamically-in-excel/
March 15th, 2010 at 11:08 am
[...] You can find the question here. [...]