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