Question: How do I group cell values on same row? The criteria is the number before the hyphen. See picture below.

Answer: Formula in B1:G1: (See picture below)

=IF(COLUMN()-1<=SUM(IF(LEFT(A1, FIND("-", A1)-1)=LEFT(List, FIND("-", List)-1), 1, 0))-1, INDEX(List, SMALL(IF((LEFT(A1, FIND("-", A1)-1)=LEFT(List, FIND("-", List)-1))*(A1<>List), ROW(List), ""), COLUMN()-1)), "") + Ctrl+ Shift + Enter. Copied down to A13:G13.

Named ranges
List (A1:A13)
What is named ranges?

Here is the result:

Download excel example file
grouping-like-together.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

ROW(reference) Returns the rownumber of a reference

COLUMN(reference) Returns the column number of a reference

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

SUM(number1,[number2],) Adds all the numbers in a range of cells

LEFT(text;num_chars) Returns the specified number of characters from the start of textstring

FIND(find_text;within_text;[start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive