Group by SSN?

[sandersen]

Anyone, I have a large spreadsheet of SSN's in Column A where some SSN's are in multiple rows. I have sorted by SSN but now I would like to have the Rows Grouped and Outlined by the SSN's in Column A. I would like use a MACRO to complete this task. Please help if you can. Thanks again in advance.

 

[GlennUK]

If you have a group of say 3 SSN's together, which of the 3 would you want demoted?

 

[sandersen]

Honestly it doesn't matter, just as long as one row is still showing.

 

[GlennUK]

It'd be something like this:

   For Each c In Range("A2", Range("A65536").End(xlUp))
        If c.Value = c.Offset(-1, 0).Value Then
            c.EntireRow.Group
        End If
    Next

 

[sandersen]

That work perfectly thanks! I have one more request if you don't mind. Is there a way to highlight every other group a color?

 

[GlennUK]

You could use conditional formatting ... like this ... condition of formula is:

=MOD(ROUND(SUM(1/COUNTIF($A$2:A2,$A$2:A2)),0),2)=1

assuming that the selected region starts in cell A2. Adjust all references ( but maintain the pattern of the $ signs ) if you are selecting a different area for your formatting.

 

[sandersen]

Again that works great, one last question and I will call this one good. I am having difficulty manipulating that last formula to highlight everyother field when the range of data I have is in columns A thru Z. Any suggestions?

 

[GlennUK]

Select A2:Z999 ( or whatever ), and use this formula:

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)=1

 

[sandersen]

worked perfectly. Thanks again.