SISINDOTEK

I.T Solution | I.T Training | I.T Integration System

Excel Concatenate Multiple Cells into One Cell

Concatenate Multiple Cells with Array Formula

Excel Concatenate Multiple Cells into One Cell (Solve)

This function was posted here recently (Jindon?) and does what you want, I believe:
Code:

Function JoinAll(ByVal BaseValue, ByRef rng As Range, ByVal delim As String) Dim a, i As Long a = rng.Value For i = 1 To UBound(a, 1) If a(i, 1) = BaseValue Then JoinAll = JoinAll & _ IIf(JoinAll = "", "", delim) & a(i, 2) Next End Function

Used like:
=JoinAll(Cell,Range,Delimiter)

Here it is in action:
Sheet2

A B C D
22 109876 Low Oil Low Oil, Checked On 12/12
23 109876 Checked On 12/12 Low Oil, Checked On 12/12
24 109877 Checked On 12/15 Checked On 12/15, Correct Level, New
25 109878 Correct Level Correct Level, Perfect, Correct Level
26 109877 Correct Level Checked On 12/15, Correct Level, New
27 109878 Perfect Correct Level, Perfect, Correct Level
28 109877 New Checked On 12/15, Correct Level, New
29 109878 Correct Level Correct Level, Perfect, Correct Level
Spreadsheet Formulas
Cell Formula
D22 =joinall(A22,$A$22:$B$29,", ")
D23 =joinall(A23,$A$22:$B$29,", ")
D24 =joinall(A24,$A$22:$B$29,", ")
D25 =joinall(A25,$A$22:$B$29,", ")
D26 =joinall(A26,$A$22:$B$29,", ")
D27 =joinall(A27,$A$22:$B$29,", ")
D28 =joinall(A28,$A$22:$B$29,", ")
D29 =joinall(A29,$A$22:$B$29,", ")

Excel tables to the web >> Excel Jeanie HTML 4

ReQuote by
SISINDOTEK – IT Training & Solution Provider
Jl. Sukasenang VI-6B Bandung – Jawa Barat 40124
Tel. 022-71242266 , SMS. 0812.8733.1966
info , YM. sisindotek
www.sisindotek.com , facebook.com/sisindotek , twitter.com/sisindotek

Single Post Navigation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: