Formula or macro needed for sorting complex data issue.

[malycom]

Hi

I have a list of clients and the person who manages the client along with
the address details and more for that person. (About 15 - 20 columns of data
per row)

I have to mail merge the person managing the client.

I have an issue in that in some instances, 1 person may manage 2 or more
clients (one manages 51 clients) and I need to work out a way to list the
clients in 1 letter to the person managing the clients as opposed to writing
a letter multiple times to a Client Manager. Obviously, one of them would
receive 51 letters.

This obviously means that my current Excel has some instances where there
are multiple rows listing the same Client Manager for different instances of
Clients.

I hope I have made this clear enough to understand.

Thanks in advance

Malcolm

 

[Bernie Deitrick]

Malcolm,

Word cannot do that automatically. You could use formulas to concatenate all the fields of interest
into a single field prior to the merge, but that would be hard to make look good. Probably the
easiest thing to do is write your letter in such a way that you reference an "Attached list" of
clients, and then create the attached list using data filtering.

HTH,
Bernie
MS Excel MVP

 

[malycom]

Hi Bernie

Thanks for the reply. I'm sorry, I didn't get my request accross correctly.

It's not the writing of the letter that I want answered.

Basically, if I have something like this

Client Man Client
A 1
B 2
B 3
C 4
D 5
D 6
D 7
E 8

I would like t0 have the formula or macro that would alter the look of the
sheet or create a new sheet that would look similar to this

A 1
B 2 3
C 4
D 5 6 7
E 8



I can then uses each row to pull all the data into the merged document as I
need it as the one row would contain the Manager name once only, the address
details and all of the clients in the same row. I know the one manager with
51 clients makes that row long, but it will work for my needs.....(If this
bit can be done)

Regards

 

[Bernie Deitrick]

Select a single cell in your table, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub DBtoCrossTab()
Dim myCell As Range
Dim myTable As Range
Dim mySht As Worksheet
Dim myRow As Long

Set myTable = ActiveCell.CurrentRegion

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Cross Tab").Delete
Application.DisplayAlerts = True

Set mySht = Worksheets.Add
mySht.Name = "Cross Tab"

myTable.Rows(1).EntireRow.Copy mySht.Rows(1)

Set myTable = myTable.Offset(1, 0).Resize _
(myTable.Rows.Count - 1, myTable.Columns.Count)

For Each myCell In myTable.Columns(1).Cells
If IsError(Application.Match(myCell.Value, _
mySht.Range("A:A"), False)) Then
myCell.EntireRow.Copy _
mySht.Range("A65536").End(xlUp)(2).EntireRow
Else
myRow = Application.Match(myCell.Value, _
mySht.Range("A:A"), False)
myCell.Offset(0, 1).Resize(1, myTable.Columns.Count - 1).Copy _
mySht.Cells(myRow, 256).End(xlToLeft)(1, 2)
End If
Next myCell

End Sub

 

[malycom]

Bernie

Many thanks for your help. It doesn't quite do what I am after but I think
this is definitely along the right lines.

I'm going to see if I can make changes where needed to get it to work but I
may be back for more help

Once again, thanks for your time and help.

Malcolm