## Group similar cell values on same row in excel

*Article updated on July 19, 2017*

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

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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 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.

**Contact Oscar**

You can contact me through this webpage

## Share this article