# How to group similar rows together in excel

I have a spreadsheet with about 2000 rows that I need to group together based on the contents of about 12 columns. The key here is that the rows may differ in a number of different columns, but for those specific 12 columns, if they have equivalent values for each row in each respective column, I want to group them together and assign them a number,name,whatever.

What's the best way to do this? Is there an easy way? The problem with filtering based on a certain column's value is that each column could have several different values in it, and the number of combinations is huge so for me to manually select each combination would be a huge headache.

-

Simple Way:

Create another column that is the concatenation of all 12 columns, then sort by that column.

Harder Way:

Use VBA, probably triggered off of a button.

Concatenate the 12 columns into a string, creating an array of these strings.

Sort all internally, keeping track of which row each is, then output the way you want.

-

Grouping similar rows together is a form of clustering. Wikipedia has a decent introduction to multi-dimensional cluster analysis, you can start there.

There are many techniques for performing clustering, but any of these techniques would be a challenge to implement within the context of Excel formulas.

-

You can group rows based on a similar values using this formula:

``````Example:

Name        Place

Rajesh     Markapur
Suresh     Minneapolis
Mahesh     Guntur
Mahesh     Chennai
Brijesh    Markapur
Sateesh    Bubaneshwar
Sateesh    Markapur
``````

And if you want to group these data on the basis of name as below

``````Excel col           A        B           C
Excelrow          Group    Name        Place
1                 1     Rajesh     Markapur
3                 2     Suresh     Minneapolis
4                 3     Mahesh     Guntur
5                 3     Mahesh     Chennai
``````

Use this formula, `=if (B2=B3, A1,A2+1)`

-
I think OP wants to avoid manually grouping them. Is that what you're suggesting? – Engineer Toast Sep 30 '15 at 20:29

Try pivot tables. They will let you group, summarise, filter and sort your data with a great amount of flexibility.

There are a few requirements on the way your source data should be laid out for pivoting to be most effective. Without seeing your data it is difficult to know how suitable it is.

-

Maybe you should have a look at ASAP Utilities. It's an addon for Excel that, among a lot of things, lets you do conditional row and column select. It's free (for non commercial use) so you might as well give it a try.

-