consolidate data seperated by comma

[SRAI31]

i have below data:

[TABLE="width: 298"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]KTTSET33L
[/TD]
[TD]KTTCAMI01L
[/TD]
[/TR]
[TR]
[TD]KTTSET33L[/TD]
[TD]KTTSLEEPSHORTS53L
[/TD]
[/TR]
[TR]
[TD]KTTSET33M
[/TD]
[TD]KTTCAMI01M
[/TD]
[/TR]
[TR]
[TD]KTTSET33M[/TD]
[TD]KTTSLEEPSHORTS53M
[/TD]
[/TR]
</tbody>[/TABLE]


i want this to be like

KTTSET33L KTTCAMI01L, KTTSLEEPSHORTS53L

KTTSET33M KTTCAMI01M,KTTSLEEPSHORTS53M

<tbody>
</tbody>

 

[krishak77]

Hi,

Paste is formula =CONCATENATE(A1," ",B1,", ",B2) as mentioned below
[TABLE="width: 623"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]
[TABLE="width: 623"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]KTTSET33L[/TD]
[TD]KTTCAMI01L[/TD]
[TD][/TD]
[TD]KTTSET33L KTTCAMI01L, KTTSLEEPSHORTS53L[/TD]
[/TR]
[TR]
[TD]KTTSET33L[/TD]
[TD]KTTSLEEPSHORTS53L[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KTTSET33M[/TD]
[TD]KTTCAMI01M[/TD]
[TD][/TD]
[TD]KTTSET33M KTTCAMI01M, KTTSLEEPSHORTS53M[/TD]
[/TR]
[TR]
[TD]KTTSET33M[/TD]
[TD]KTTSLEEPSHORTS53M[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hope this will help for what you are looking.

Best Regards,
Anantha[/TD]
[TD][/TD]
[TD][/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-
Your data assumed to starts "A2, B2", results start "C2".

[COLOR="Navy"]Sub[/COLOR] MG27Jan05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) & ", " & Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

Try this:-
Your data assumed to starts "A2, B2", results start "C2".

[COLOR="Navy"]Sub[/COLOR] MG27Jan05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) & ", " & Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

I posted my answer to this same question in the OP's other thread located here...

https://www.mrexcel.com/forum/excel...-seperated-comma-post4996121.html#post4996121