Merge/consolidate text cells based on unique keys ?

  • Thread starter Thread starter x13
  • Start date Start date
X

x13

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.
 
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.
Click to expand...



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.
 
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.
 
Back
Top