Excel - grouping filtered results

Dell Inspiron 1464 notebook
August 29, 2010 at 12:29:11
Specs: Windows XP

2nd attempt at posting this - it's already been posted, apologies for the duplication!

I've got a list of approx 2000 rows on a spreadsheet that has a person's name and a book assigned to them. They person can have up to 6 books, from a list of only 6 books (a book for 1 of 6 subjects).

I'm trying to group together all the people who have the same books as each other (eg, everyone who has both a french and a history book). From the data below I'd like to group John and Terry as they have the exact same books, but not Alan who has only 2 of the 3 books John and Terry have.

Here's a sample of my data:

John French book
John Maths book
John History book
Amy Maths book
Alan French book
Alan History book
Terry French book
Terry Maths book
Terry History book

Thanks in advance folks - this has been hurting my brain for the last week!!!


See More: Excel - grouping filtered results

Report •

August 29, 2010 at 19:51:01
This code is based on the strictly on the example data you have provided.

The rules are:

There is nothing you need in Columns C or D. (This can be modified)

Names are in Column A. (This can be modified)
Book Names are in Column B. (This can be modified)

Names are grouped together as shown. (This can not be modified)
Book Names are sorted as shown. (This can not be modified)

i.e. Both John and Terry's books are listed as:

French book
Maths book
History book

Not one as this: ..... and the other as this:

French book           Maths book
Maths book            History book
History book          French book

They must be in the same order.

The reason for this is because of how the code works.

The code places a filtered list of unique Names in Column C

It then loops through the original list of names and builds a Concatenated string made up of the Book Names for each individual and places it next to each name in the filtered list. (Column D)

Finally, it sorts the list of strings which will group the Names together.

If the list of Book Names is not in the same order for each person, the string will not match and will not get sorted with the others.

In other words, French book Maths book History book is not the same as French book History book Maths book.

Try this code in a back up copy of your workbook since macro can not be undone.

Remember: As written, it will overwrite whatever you have in Columns C and D.

Option Explicit
Sub GroupBooks()
Dim lastName_rw, rw, nxtRw, lastFiltered_rw As Integer
'Clear Column C & D
'Put Column heading in D
 Cells(1, 4) = "BOOK TITLE STRING"
'Find last used Row in Column A
 lastName_rw = Range("A" & Rows.Count).End(xlUp).Row
'Create a filtered list of Names in Column C
 Range("A1:A" & lastName_rw).AdvancedFilter Action:=xlFilterCopy, _
       CopyToRange:=Range("C1"), Unique:=True
'Initialize Row Counter
 nxtRw = 2
'Loop through Names in Column A
  For rw = 2 To lastName_rw
'If the Name in a row matches the name below it,
'build a concatentated string of book titles next
'to that Name in Column D
   If Cells(rw, 1) = Cells(rw + 1, 1) Then
    Cells(nxtRw, 4) = Cells(nxtRw, 4) & Cells(rw, 2) & " "
'If Name doesn't match the next name
'add the last book title
   Cells(nxtRw, 4) = Cells(nxtRw, 4) & Cells(rw, 2) & " "
'Increment Row Counter to move to next Name in Column C
   nxtRw = nxtRw + 1
   End If
'Once all Book Title Strings have been built...
'Find last Row with a name in Column C
  lastFiltered_rw = Range("A" & Rows.Count).End(xlUp).Row
'Sorts Names and Book Titles Strings, key on Book Title String
  Range("C1:D" & lastFiltered_rw).Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End Sub
Sub Macro3()
' Macro3 Macro
' Macro recorded 08/29/2010 by Dave

    Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "C1"), Unique:=True
End Sub

Report •
Related Solutions

Ask Question