grouping data

  • Thread starter Thread starter sbp
  • Start date Start date
S

sbp

in excel i have a huge list of people.
the headings are last name/first name/Event name/Event
year.
If some people attend 5 events it lists them 5 times.
I would like my end result to list everyone once and what
events (if any) they attended and what year.

how do i do this?

IE.
currently i have
Smith/Bob/GST Seminar/2004
Smith/Bob/Tax conference/2003
Doe/Jane/GST Seminar/2000
doe/Jane/Accounting Seminar/1999

End result i would like:
Smith/Bob GST Seminar/2004
Tax conference/2003

Doe/Jane GST Seminar/2004
Accounting Seminar/ 1999
 
If the slashes represent columns you should be able to
modify macro located via
Joining Codes in Column B to a Product in Column A (#joining)
http://www.mvps.org/dmcritchie/excel/snakecol.htm#joining

Though your example looks more like you want to wipe out
cells in Column A & B if they match column A & B above, so
all data remains on original rows. Which is actually easier
but not much point to it. You would not be able to
sort such a list. Though with the macro you would best be
creating a new sheet for your display anyway. If that is
what you want it might be better to just bold Column A&B
at the beginning or to use data, subtotals
 
On the alternative, Data, Subtotals would not work but if this
is what you want

A1 B1 C1 D1 E1 A1 B1 C1 D1 E1
A1 B1 C2 D2 E2 --- --- C2 D2 E2
A1 B1 C3 D3 E3 ---- -- C3 D3 E3
A4 B4 C4 D4 E4 A4 B4 C4 D4 E4
A4 B4 C5 D5 E5 --- --- C5 D5 E5
A4 B4 C6 D6 E6 --- --- C6 D6 E6
A4 B4 C7 D7 E7 --- --- C7 D7 E7
A8 B8 C8 D8 E8 A8 B8 C8 D8 E8
A8 B8 C9 D9 E9 --- --- C9 D9 E9

Then this macro would do it; otherwise see the
macro I referred you to before that would need changing.

Sub cleardupAandB()
Dim lastrow As Long, i As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) Then
Cells(i, 1) = ""
Cells(i, 2) = ""
End If
Next i
End Sub

If not familiar with installing/using macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
David - thanks so much. this works perfectly. how do i
change the macro if instead of column A and B matching
how about columns A through G

Sub cleardupAandB()
'D.McRitchie, 2004-08-11 snakecol.htm
Dim lastrow As Long, i As Long
lastrow = Cells(Cells.Rows.Count, _
"A").End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) _
And Cells(i, 2) = Cells(i - 1, 2) Then
Cells(i, 1) = ""
Cells(i, 2) = ""
End If
Next i
End Sub


IE. my spreadsheet is:

IDno./Title/First/Last/Initial/city/Prov/Event
123/Mr/Jane/Doe/D./Toronto/ON/Seminar GST
123/Mr/Jane/Doe/D./Toronto/ON/Webinar PST
123/Mr/Jane/Doe/D./Toronto/ON/Conference CTS

My end result I would like is:

123/Mr/Jane/Doe/D./Toronto/ON/Seminar GST
Webinar PST
Conference CTS
 
one way would be to keep adding in more ANDs
the second parameter of CELLS is the column number,
and to also clear out the cells below that accordingly,
you should be able to change that if not post back.
 
Thanks so much!!
-----Original Message-----
one way would be to keep adding in more ANDs
the second parameter of CELLS is the column number,
and to also clear out the cells below that accordingly,
you should be able to change that if not post back.
---
HTH,
David McRitchie, Microsoft MVP - Excel

"SBP" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top