selection for output

[49niner]

I have a list with different companies in column A. My problem is I have to
create a report that should only print the name for each company once and
list the staff matched to the company under the company name.

example: A B C D E
F
ACME Jones Sally
MINOR Brown Jack
FLOSS Kahn Shri
DUNKIN Cow Holy
ACME TT KK
ACME JJ BB
MINOR Notes Keys

Report example:
A B C D E F
ACME
TT KK
JJ BB
Jones Sally

DUNKIN
Cow Holy

FLOSS
Kahn Shri

MINOR
Brown Jack
Notes Keys


If anyone can resolve this for me I would greatly appreicate it!



53214920

 

[Joel]

The sode below assumes the source sheet is Sheet1 and creates a new worksheet
called report. It then sorts the data to get all the company names in order.
Then formats the data the way you specified.

Sub make_report()

Sheets("Sheet1").Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Report"
With Sheets("Report")
.Cells.Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess

RowCount = 1
CompanyName = ""
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) <> CompanyName Then
CompanyName = .Range("A" & RowCount)
.Rows(RowCount).Insert
.Range("A" & RowCount) = CompanyName
RowCount = RowCount + 1
End If
.Range("A" & RowCount) = ""
RowCount = RowCount + 1
Loop
End With

End Sub