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