Search and Combine Rows


K

ktemplin

I have a spreadsheet that has multiple rows and two Colums. A1 =
Server name, B1 = email address of the server owner. The problem is
that servers have multiple owners so one server could have 7 rows.
What I would like to do is to concatinate all the email addresses, so
I have one row per server.
For example here is the existing data:
Row = Column1, Column2
Row 1 = Server1, (e-mail address removed)
Row 2 = Server1, (e-mail address removed)
Row 3 = Server1, (e-mail address removed)
Row 4 = Server1, (e-mail address removed)
Row 5 = Server2, (e-mail address removed)
Row 6 = Server2, (e-mail address removed)
Row 7 = Server3, (e-mail address removed)

What I would like to have is:
Row 1 = Server1, (e-mail address removed); (e-mail address removed);
(e-mail address removed); (e-mail address removed)
Row 2 = Server2, (e-mail address removed); (e-mail address removed)
Row 3 = Row 7 = Server3, (e-mail address removed)

I am not familiar with macros, is there any way of doing this with
functions?
 
Ad

Advertisements

D

Don Guillett

I have a spreadsheet that has multiple rows and two Colums. A1 =
Server name, B1 = email address of the server owner. The problem is
that servers have multiple owners so one server could have 7 rows.
What I would like to do is to concatinate all the email addresses, so
I have one row per server.
For example here is the existing data:
Row = Column1, Column2
Row 1 = Server1, (e-mail address removed)
Row 2 = Server1, (e-mail address removed)
Row 3 = Server1, (e-mail address removed)
Row 4 = Server1, (e-mail address removed)
Row 5 = Server2, (e-mail address removed)
Row 6 = Server2, (e-mail address removed)
Row 7 = Server3, (e-mail address removed)

What I would like to have is:
Row 1 = Server1, (e-mail address removed); (e-mail address removed);
(e-mail address removed); (e-mail address removed)
Row 2 = Server2, (e-mail address removed); (e-mail address removed)
Row 3 = Row 7 = Server3, (e-mail address removed)

I am not familiar with macros, is there any way of doing this with
functions?
Sub blockstorowsSAS()
Dim i As Long
Dim lc As Long

On Error Resume Next
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i + 1, 1) = Cells(i, 1) Then
lc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
Cells(i + 1, 2).Resize(, lc).Copy Cells(i, lc)
Rows(i + 1).Delete
End If
Next i
Columns.AutoFit
End Sub
 
V

Vacuum Sealed

I have a spreadsheet that has multiple rows and two Colums. A1 =
Server name, B1 = email address of the server owner. The problem is
that servers have multiple owners so one server could have 7 rows.
What I would like to do is to concatinate all the email addresses, so
I have one row per server.
For example here is the existing data:
Row = Column1, Column2
Row 1 = Server1, (e-mail address removed)
Row 2 = Server1, (e-mail address removed)
Row 3 = Server1, (e-mail address removed)
Row 4 = Server1, (e-mail address removed)
Row 5 = Server2, (e-mail address removed)
Row 6 = Server2, (e-mail address removed)
Row 7 = Server3, (e-mail address removed)

What I would like to have is:
Row 1 = Server1, (e-mail address removed); (e-mail address removed);
(e-mail address removed); (e-mail address removed)
Row 2 = Server2, (e-mail address removed); (e-mail address removed)
Row 3 = Row 7 = Server3, (e-mail address removed)

I am not familiar with macros, is there any way of doing this with
functions?

Row = Column3

=Column1&","&Column2

HTH
Mick.
 
V

Vacuum Sealed

Row = Column3

=Column1&","&Column2

HTH
Mick.
D'oh

Pays to read the post in it's entirety.....
Holiday Mode...:)

What Don has posted is what you need ....

Mick.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top