How to generate concatenated list of values?

[jb]

I have a table of 20 rows and 2 columns.

User will fill data everyday in this table. It depend on user that how many rows he/she will fill. But it will not go beyond 20 rows.

For e.g

[pre]

ABC   AA
JKL   AA
PQR   BB
LMN   CC
DEF   CC

[/pre]
I want to generate concatenated list at bottom of table like:

ABC, JKL (AA); PQR (BB); LMN , DEF (CC)

If second column is same for multiple rows then after separating first column values by comma the second column value should come only once.

It is not fixed that how many times second column will repeat.

Help me.

 

[shrivallabha]

Hi jb,


You can try this code and see if this works for you.

[pre]

Public Sub ConcatenateData()
Dim k
Dim r As Range, rng As Range
Dim strList As String

'Set table reference here
Set rng = Range("A2:B21")

'Create object which checks for repeats
With CreateObject("Scripting.Dictionary")

'Building and stacking up data
For Each r In Range(rng.Columns(2).Address)
If .exists(r.Value) Then
.Item(r.Value) = .Item(r.Value) & ", " & r.Offset(0, -1).Value
ElseIf Len(r.Value) > 0 Then
.Add r.Value, r.Offset(0, -1).Value
End If
Next r

strList = vbNullString

'Building up string from compiled data
For Each k In .keys
If strList = vbNullString Then
strList = .Item(k) & " (" & k & ")"
Else
strList = strList & "; " & .Item(k) & " (" & k & ")"
End If
Next k

'Set this to suitable range
Range("A22").Value = strList

End With
End Sub

[/pre]

 

[Marc L]

Hi jb !   This is a more convenient fastest way (function in a normal module) :

[pre]

Function ConcatList(Rg As Range) As String
Application.Volatile

If Rg.Columns.Count > 1 Then
A = Rg
N = UBound(A)

For R = LBound(A) To N
S = S & A(R, 1)

If R = N Then
S = S & " (" & A(R, 2) & ")"
ElseIf A(R, 2) = A(R + 1, 2) Then
S = S & ", "
Else
S = S & " (" & A(R, 2) & "); "
End If
Next

ConcatList = S
End If
End Function[/pre]
If the table is in E3:F25, enter this formula in the cell you want the concatenated list :   =ConcatList(E3:F25)

Enjoy !

 

[shrivallabha]

Application.Volatile is not needed as you are covering the whole range. It will slow down the code

 

[Marc L]

Yes it's not really needed in this case but it won't slow down the code

'cause its using a memory array instead of scanning cells …


I like using dictionary for an unsorted list or with blank between cells for example.

But in this case (sorted list without blank), using a memory array is faster than scanning cells and build a dictionary …


For only 20 rows and 2 columns, really no matter.       But with thousands rows, huge is the time gain with an array !

 

[shrivallabha]

Sorry, I have stated it wrongly:

It will cause the function to re-calc every time even if the 'precedents' are not changed which is bad.


With 20 cells I wouldn't care about the method as long as it is giving right results. The method a person is comfortable with, should be the one he should use.


What I liked about your approach is that it is a function so it will behave dynamically i.e. as soon as change is made the result will be altered.


Note: Posted data is sorted but why take a chance?