Problem displaying multiple values

  • Thread starter Thread starter Sue at VLS
  • Start date Start date
S

Sue at VLS

Hello. I have extracted a fair bit of data from out
database. This data has name, id#, program, interest area.

Some names in my extraction have indicated multiple
interest areas. Such that my spreadsheet (extracted data)
currently has several rows for each person - - i.e. person
John Doe may have 8 rows with duplicate information EXCEPT
that each row contains a different area of interest.

I would like to format this worksheet such that each
person is listed once and then each area of interest are
in columns across the page.

How would I do this? I thought about a pivot table, but I
don't think that is exactly what I need.

Thanks in advance for any help you can provide.

Sue at VLS
 
I think that this will work ok.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim TopCell As Range

Set wks = Worksheets("sheet1")

With wks
Set TopCell = .Range("B2")
Do
If IsEmpty(TopCell.Offset(1, 0)) Then
Exit Do
Else
If TopCell.Value = TopCell.Offset(1, 0).Value Then
.Cells(TopCell.Row, .Columns.Count) _
.End(xlToLeft).Offset(0, 1).Value _
= .Cells(TopCell.Row + 1, TopCell.Column + 2).Value
TopCell.Offset(1, 0).EntireRow.Delete
Else
Set TopCell = TopCell.Offset(1, 0)
End If
End If
Loop
End With

End Sub

I used the ID# (in column B) as my key. And I expected them in order!
B2 was the first (headers in row 1).

I put the area of interest in column D.
..Cells(TopCell.Row + 1, TopCell.Column + 2).Value
refers to the next row down and two to the right.

If I guessed wrong, change that line to match.

and change
Set TopCell = .Range("B2")
to the real first cell with an ID#.

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top