consolidate data seperated by comma

SRAI31 · Jan 27, 2018

we had few data like below

 [TABLE="class: MsoNormalTable, width: 276"]
<tbody>[TR]
[TD="width: 235"] KTTSET33L
[/TD]
[TD="width: 134"] KTTCAMI01L
[/TD]
[/TR]
[TR]
[TD="width: 235"] KTTSET33L
[/TD]
[TD="width: 134"] KTTSLEEPSHORTS53L
[/TD]
[/TR]
[TR]
[TD="width: 235"] KTTSET33M
[/TD]
[TD="width: 134"] KTTCAMI01M
[/TD]
[/TR]
[TR]
[TD="width: 235"] KTTSET33M
[/TD]
[TD="width: 134"] KTTSLEEPSHORTS53M
[/TD]
[/TR]
</tbody>[/TABLE]

we want to consolidate it against left columb , and the result should look like:


 [TABLE="class: MsoNormalTable, width: 276"]
<tbody>[TR]
[TD="width: 235"] KTTSET33L
[/TD]
[TD="width: 134"] KTTCAMI01L,

KTTSLEEPSHORTS53L

[/TD]
[/TR]
[TR]
[TD="width: 235"]KTTSET33M

[/TD]
[TD="width: 134"] KTTCAMI01M,

KTTSLEEPSHORTS53M

[/TD]
[/TR]
[TR]
[TD="width: 235"][/TD]
[TD="width: 134"][/TD]
[/TR]
[TR]
[TD="width: 235"][/TD]
[TD="width: 134"][/TD]
[/TR]
</tbody>[/TABLE]

Rick Rothstein · Jan 27, 2018

Give this macro a try...

Code:

[table="width: 500"]
[tr]
	[td]Sub ConsolidateData()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A2:A" & LastRow) = Evaluate("IF(A2:A" & LastRow & "=A1:A" & LastRow - 1 & ","""",A2:A" & LastRow & ")")
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1, 1) = Join(Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Count + 1)), ", ")
  Next
  Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

Rick Rothstein · Jan 27, 2018

Duplicate posting...

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

SRAI31 · Jan 27, 2018

Thanx. It works

Tom.Jones · Jan 28, 2019

Rick Rothstein said:

Give this macro a try...

Code:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ConsolidateData()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A2:A" & LastRow) = Evaluate("IF(A2:A" & LastRow & "=A1:A" & LastRow - 1 & ","""",A2:A" & LastRow & ")")
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1, 1) = Join(Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Count + 1)), ", ")
  Next
  Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Rick,

Please, can you modify VBA code to have result in other sheet? (without overwriting the data in the sheet)

Thank you.

sandy666 · Jan 28, 2019

another way with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]KTTSET33L[/td][td=bgcolor:#DDEBF7]KTTCAMI01L[/td][/tr]

[tr=bgcolor:#FFFFFF][td]KTTSET33L[/td][td]KTTSLEEPSHORTS53L[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]KTTSET33M[/td][td=bgcolor:#DDEBF7]KTTCAMI01M[/td][/tr]

[tr=bgcolor:#FFFFFF][td]KTTSET33M[/td][td]KTTSLEEPSHORTS53M[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]KTTSET33L[/td][td=bgcolor:#E2EFDA]KTTCAMI01L,
KTTSLEEPSHORTS53L[/td][/tr]

[tr=bgcolor:#FFFFFF][td]KTTSET33M[/td][td]KTTCAMI01M,
KTTSLEEPSHORTS53M[/td][/tr]
[/table]

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Column2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ",#(lf)"), type text})
in
    #"Extracted Values"[/SIZE]

consolidate data seperated by comma

SRAI31

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

SRAI31

New Member

Tom.Jones

Well-known Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

consolidate data seperated by comma

SRAI31

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

SRAI31

New Member

Tom.Jones

Well-known Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock