Excel - Sorting Groups of Duplicate Data

**chrisjc19** · 11-04-2011, 11:01 AM

Hi,

I have sorted a list by a number of levels within excel to find groups of data. I would now like to order those groups of data from the highest frequency of duplicate values to the lowest frequency of duplicate values.

E.g. Currently my excel sheet list looks like this:

Column Column Column Column Column
A B C D E
No | Applicant Name | Applicant Surname | Respondent Name | Respondent Surname
1 A B C D
2 A B C D
3 E F G H
4 I J K L
5 I J K L
6 I J K L
7 M N O P

However, I would like to keep the data together but order the data from the largest amount of duplicate rows to the least amount of duplicate rows (i.e. keeping the entire row together where the values from columns B-E are the same), so it would look instead like this:

Column Column Column Column Column
A B C D E
No | Applicant Name | Applicant Surname | Respondent Name | Respondent Surname
4 I J K L
5 I J K L
6 I J K L
1 A B C D
2 A B C D
3 E F G H
7 M N O P

How can this be done? Does it require a macro of some sort? I have looked all over the internet for an answer so help would be great...!

Thanks, Chris

**chrisjc19** · 11-04-2011, 11:04 AM

p.s. and if anyone then knows how to make the background cell color for each group of duplicates change when there is different unique group of duplicates, that would be even more great!

Eg:

FFFFFF
FFFFFF
FFFFFF
FFFFFF
AAAAAA
AAAAAA
AAAAAA
BBBBBB
BBBBBB
JJJJJJ

**foxguy** · 11-04-2011, 04:48 PM

Cell G1: =B1&C1&D1&E1
Cell H1: =Countif(G:G,G1)

Copy both formulas down the column, then sort A:H on column H

You can alternate between 2 colors between different cells using conditional formatting, but to have it have more than 2 colors it takes a macro to permanently change the color.

**chrisjc19** · 11-04-2011, 06:00 PM

Thanks heaps Foxguy! Your solution for my first post works great - you're a genius!

However, I'm still having trouble with the second part in coloring the background of the rows where there is change in the 'duplicate groups'. I'm happy for only two colors to alternate or to have many colors as long as the different groups of duplicates can be distinguished. It does not seem obvious how conditional formatting achieves this as it seems to only allow highlighting of 'all' duplicate values. For example, using the column G that you suggested I add, the colors in the rows could alternate based on this column if possible, such as:

Column G

I J K L
I J K L
I J K L
A B C D
A B C D
E F G H
E F G H
M N O P

All the other columns in the same row would have the matching colour.

Thanks once again for your assistance.

**chrisjc19** · 11-04-2011, 06:01 PM

Thanks heaps Foxguy! Your solution for my first post works great - you're a genius!

However, I'm still having trouble with the second part in coloring the background of the rows where there is change in the 'duplicate groups'. I'm happy for only two colors to alternate or to have many colors as long as the different groups of duplicates can be distinguished. It does not seem obvious how conditional formatting achieves this as it seems to only allow highlighting of 'all' duplicate values. For example, using the column G that you suggested I add, the colors in the rows could alternate based on this column if possible, such as:

Column G

I J K L
I J K L
I J K L
A B C D
A B C D
E F G H
E F G H
M N O P

All the other columns in the same row would have the matching colour.

Thanks once again for your assistance...!

**foxguy** · 11-04-2011, 06:09 PM

I messed up on the conditional formatting.
Try this:

Cell J2: =J1+If(G2<>G1,1,0) 'skip row 1 - just put a 0 in J1
copied down the column

Conditional Formating A1:E:1 =IsEven($J1) 'format Green backgound