selection for output
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
"49niner" wrote:
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
|