PDA

View Full Version : Capturing headings of frequent data



rrahl
08-27-2008, 01:56 PM
I have data from a card sorting survey that I'm trying to group into category headings.

The table has card names across the top and each row has the data for each person who did the card sorting exercise with the data being the group name that they put each card name into. The total unique group names is 84 scattered throughout the table.

I want to create a new table that has the 84 possible group names across the top with all the card names under it that were put in that group.

So what I want VBA code to do is do a search for the first of the 84 group names and whenever that word is found in the data table determine what card name is in the column heading and paste that in a new table under a column for the group name.

It will then loop through looking for the next word of the 84 and paste the results into a new column, etc.

This is what I've got so far:

Sub Macro3()

'Groupings sheet is a new table with the 84 group names across the top
Sheets("Groupings").Select

Range("A1").Range("A1").Select

Do

GroupName = ActiveCell.FormulaR1C1
'Groups sheet is the data table with all the group names that

'people entered for each of the 45 cards
Sheets("Groups").Select
Range("B3:AO156").Select
Selection.Find(What:=GroupName, _

LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False).Activate

Range(column of cell that matches,top cell).Select

'paste this onto Groups sheet in column 1

'it needs to loop back here finding the next instance and

'repeat the copy and paste
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub