Hi all.

I hope someone can help me out.

I have an Excel worksheet with 2 columns: 'Client #' and 'Invoice #'.

Every time the accounting dept. generates an invoice, a new row is
added in this worksheet.
Obviously this is chronological not per Client #.

But for the sake of simplicity, let's assume the worksheet is already
sorted by Client #, like so:

A B

Client # Invoice #

231 5929
231 4358
231 2185
231 6234
464 1166
464 1264
464 3432
464 1720
464 9747
791 1133
791 4930
791 5496
791 6291
989 8681
989 3023
989 7935
989 8809
989 8873

My goal is to achieve this:

Client # Invoice #

231 5929, 4358, 2185, 6234
464 1166, 1264, 3432, 1720, 9747
791 1133, 4930, 5496, 6291
989 8681, 3023, 7935, 8809, 8873


In order to create a (Word) mail-merge, where I can write to each
Client:


"Dear ABC,

You have the following invoices are still open: <column B from the
optimised version..."


Anyone have an idea how to achieve this without external software or
VB programming?

Any help greatly appreciated.
==
M.T.

On May 23, 12:50*pm, x13 wrote:
Hi all.

I hope someone can help me out.

I have an Excel worksheet with 2 columns: 'Client #' and 'Invoice #'.

Every time the accounting dept. generates an invoice, a new row is
added in this worksheet.
Obviously this is chronological not per Client #.

But for the sake of simplicity, let's assume the worksheet is already
sorted by Client #, like so:

* *A * * * * * * B

Client # * * * *Invoice #

231 * * 5929
231 * * 4358
231 * * 2185
231 * * 6234
464 * * 1166
464 * * 1264
464 * * 3432
464 * * 1720
464 * * 9747
791 * * 1133
791 * * 4930
791 * * 5496
791 * * 6291
989 * * 8681
989 * * 3023
989 * * 7935
989 * * 8809
989 * * 8873

My goal is to achieve this:

Client # * * * *Invoice #

231 * * 5929, 4358, 2185, 6234
464 * * 1166, 1264, 3432, 1720, 9747
791 * * 1133, 4930, 5496, 6291
989 * * 8681, 3023, 7935, 8809, 8873

In order to create a (Word) mail-merge, where I can write to each
Client:

"Dear ABC,

You have the following invoices are still open: <column B from the
optimised version..."

Anyone have an idea how to achieve this without external software or
VB programming?

Any help greatly appreciated.
==
M.T.



Until you get a non-VB solution, here is a VBA solution:


Sub ReOrganize()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim cl As Collection
Set cl = New Collection
Dim i As Long, j As Long, k As Long, l As Long
Dim jj As Long
j = 1
k = s1.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For i = 1 To k
v = s1.Cells(i, 1).Value
Err.Clear
cl.Add v, CStr(v)
If Err.Number = 0 Then
s2.Cells(j, 1).Value = v
jj = 2
For l = i To k
If s1.Cells(l, 1).Value = v Then
s2.Cells(j, jj).Value = s1.Cells(l, 2).Value
jj = jj + 1
End If
Next
j = j + 1
End If
Next
End Sub



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi James.

First thanks for your help!

You're right. That cell manipulations I require -- albeit simple if
done manually -- are a bit too intricate for the available built-in
functions.
So VB is fine.

On the first run I got a 'Subscript out of range' error on 'Set s2 =
Sheets("Sheet2")' because that worksheet didn't exist. Obviously.
Your module works fine, but it creates a table of results of variable
width, whereas I need to begin -- and end -- with 2 columns.
The second column must be a concatenation, but your module creates as
many extra columns as the client has invoices.

This complicates things for the next step: using Sheet2 as input for a
Word mail merge.
Since I can't know it advance how wide the output table will be, I'd
have to make the selection unnecessarily wide.
Plus I can't identify multiple columns using a single column header.

Not to impose, but could (you tell me how to ) modify your module so
that each new invoice number is concatenated into the second column?
If I have the 2 columns to work with then I can figure out the rest.


Thanks!
M.T.


On May 29, 5:00*pm, James Ravenswood
wrote:
On May 23, 12:50*pm, x13 wrote:





Hi all.

I hope someone can help me out.

I have an Excel worksheet with 2 columns: 'Client #' and 'Invoice #'.

Every time the accounting dept. generates an invoice, a new row is
added in this worksheet.
Obviously this is chronological not per Client #.

But for the sake of simplicity, let's assume the worksheet is already
sorted by Client #, like so:

* *A * * * * * * B

Client # * * * *Invoice #

231 * * 5929
231 * * 4358
231 * * 2185
231 * * 6234
464 * * 1166
464 * * 1264
464 * * 3432
464 * * 1720
464 * * 9747
791 * * 1133
791 * * 4930
791 * * 5496
791 * * 6291
989 * * 8681
989 * * 3023
989 * * 7935
989 * * 8809
989 * * 8873

My goal is to achieve this:

Client # * * * *Invoice #

231 * * 5929, 4358, 2185, 6234
464 * * 1166, 1264, 3432, 1720, 9747
791 * * 1133, 4930, 5496, 6291
989 * * 8681, 3023, 7935, 8809, 8873

In order to create a (Word) mail-merge, where I can write to each
Client:

"Dear ABC,

You have the following invoices are still open: <column B from the
optimised version..."

Anyone have an idea how to achieve this without external software or
VB programming?

Any help greatly appreciated.
==
M.T.

Until you get a non-VB solution, here is a VBA solution:

Sub ReOrganize()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim cl As Collection
Set cl = New Collection
Dim i As Long, j As Long, k As Long, l As Long
Dim jj As Long
j = 1
k = s1.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For i = 1 To k
* * v = s1.Cells(i, 1).Value
* * Err.Clear
* * cl.Add v, CStr(v)
* * If Err.Number = 0 Then
* * * * s2.Cells(j, 1).Value = v
* * * * jj = 2
* * * * For l = i To k
* * * * * * If s1.Cells(l, 1).Value = v Then
* * * * * * * * s2.Cells(j, jj).Value = s1.Cells(l, 2).Value
* * * * * * * * jj = jj + 1
* * * * * * End If
* * * * Next
* * * * j = j + 1
* * End If
Next
End Sub

Macros are very easy to install and use:

1. ALT-F11 *brings up the VBE window
2. ALT-I
* * ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

* *1. bring up the VBE window as above
* *2. clear the code out
* *3. close the VBE window

To use the macro from Excel:

* *1. ALT-F8
* *2. Select the macro
* *3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

On Fri, 3 Jun 2011 06:54:19 -0700 (PDT), x13 wrote:

Wow!

Thanks to both James and Ron for your assistance.
The last version of Ron's solution has solved my problem.

PS: The placement of the resulting table seems arbitrary (not relative
to cursor position when running the macro), but I can live with it. ;)

M.T.


Glad to help. Thanks for the feedback.

With regard to the table placement, that is arbitrary but defined within the macro.

These lines near the end define the placement of the resulting table:

---------
With Range("F1", Cells(coll.Count, "G"))
.Value = vRes
.EntireColumn.AutoFit
End With
-------

You need to define a range that is the same size and shape of the resultant vRes array.

I arbitrarily chose to put it into F1:Gn where n is the number of rows in the vRes array, and should be the same as the number of entries in the coll collection.

But you can put it anyplace. Just be sure that you are only defining two columns in your resultant range.