I'm trying to manipulate data to show the individuals in a company only on
one line. The most employees I will show is 64 (so I would have 64 blank
columns to the left of the company name).

On column A is the company name - so if I have 3 contact names for company
ABC, they would take up three rows. What I would like to do is show only
one company and have the names listed to the right of the company name all
on one row. After the names have moved, delete the extra rows.

Can someone help me with code to move the contacts to show only one company
with the different contacts.

(The contacts need to be in their own column as this spreadsheet will be
merged later into a Word document.)

Thanks for any help you can provide.

Annette

If your company names are in column A and the individuals are in column
B and your data is sorted by column A then try this: [Save your data
before testing]

Sub CNames()
Dim cName As String
Dim eRow As Long
Dim i As Long
Dim k As Integer
k = 2
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = eRow To 2 Step -1
cName = Cells(i, 1).Value
If Cells(i - 1, 1).Value = cName Then
Range(Cells(i, 2), Cells(i, k)).Copy Cells(i - 1, 3)
k = k + 1
Rows(i).EntireRow.Delete
Else
k = 2
End If
Next i
End Sub

Hope this helps
Rowan

Annette wrote:
I'm trying to manipulate data to show the individuals in a company only on
one line. The most employees I will show is 64 (so I would have 64 blank
columns to the left of the company name).

On column A is the company name - so if I have 3 contact names for company
ABC, they would take up three rows. What I would like to do is show only
one company and have the names listed to the right of the company name all
on one row. After the names have moved, delete the extra rows.

Can someone help me with code to move the contacts to show only one company
with the different contacts.

(The contacts need to be in their own column as this spreadsheet will be
merged later into a Word document.)

Thanks for any help you can provide.

Annette

This works great ... but what if I wanted to add and include the name
(column B) and the title (column C) -- how would I modify this to include
and move the title next to the name?


"Rowan Drummond" wrote in message
...
If your company names are in column A and the individuals are in column B
and your data is sorted by column A then try this: [Save your data before
testing]

Sub CNames()
Dim cName As String
Dim eRow As Long
Dim i As Long
Dim k As Integer
k = 2
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = eRow To 2 Step -1
cName = Cells(i, 1).Value
If Cells(i - 1, 1).Value = cName Then
Range(Cells(i, 2), Cells(i, k)).Copy Cells(i - 1, 3)
k = k + 1
Rows(i).EntireRow.Delete
Else
k = 2
End If
Next i
End Sub

Hope this helps
Rowan

Annette wrote:
I'm trying to manipulate data to show the individuals in a company only
on one line. The most employees I will show is 64 (so I would have 64
blank columns to the left of the company name).

On column A is the company name - so if I have 3 contact names for
company ABC, they would take up three rows. What I would like to do is
show only one company and have the names listed to the right of the
company name all on one row. After the names have moved, delete the
extra rows.

Can someone help me with code to move the contacts to show only one
company with the different contacts.

(The contacts need to be in their own column as this spreadsheet will be
merged later into a Word document.)

Thanks for any help you can provide.

Annette

Try it like this:

Sub CNames()
Dim cName As String
Dim eRow As Long
Dim i As Long
Dim k As Integer
k = 3
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = eRow To 2 Step -1
cName = Cells(i, 1).Value
If Cells(i - 1, 1).Value = cName Then
Range(Cells(i, 2), Cells(i, k)).Copy Cells(i - 1, 4)
k = k + 2
Rows(i).EntireRow.Delete
Else
k = 3
End If
Next i
End Sub

Regards
Rowan

Annette wrote:
This works great ... but what if I wanted to add and include the name
(column B) and the title (column C) -- how would I modify this to include
and move the title next to the name?


"Rowan Drummond" wrote in message
...

If your company names are in column A and the individuals are in column B
and your data is sorted by column A then try this: [Save your data before
testing]

Sub CNames()
Dim cName As String
Dim eRow As Long
Dim i As Long
Dim k As Integer
k = 2
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = eRow To 2 Step -1
cName = Cells(i, 1).Value
If Cells(i - 1, 1).Value = cName Then
Range(Cells(i, 2), Cells(i, k)).Copy Cells(i - 1, 3)
k = k + 1
Rows(i).EntireRow.Delete
Else
k = 2
End If
Next i
End Sub

Hope this helps
Rowan

Annette wrote:

I'm trying to manipulate data to show the individuals in a company only
on one line. The most employees I will show is 64 (so I would have 64
blank columns to the left of the company name).

On column A is the company name - so if I have 3 contact names for
company ABC, they would take up three rows. What I would like to do is
show only one company and have the names listed to the right of the
company name all on one row. After the names have moved, delete the
extra rows.

Can someone help me with code to move the contacts to show only one
company with the different contacts.

(The contacts need to be in their own column as this spreadsheet will be
merged later into a Word document.)

Thanks for any help you can provide.

Annette