Put comma seperated data into single columb against same vallue

SRAI31 · Jan 27, 2018

I HAD SOME DATA LIKE BELOW:

X	A,B,C
Y	D,E,F

I WANT TO MAKE IT LIKE :

X	A
X	B
X	C
Y	D
Y	E
Y	F

KINDLY HELP

pgc01 · Jan 27, 2018

Hi

Is this not the same question (for which you got an anwer and did not reply)?

https://www.mrexcel.com/forum/excel...-comma-seperated-data-into-single-columb.html

SRAI31 · Jan 27, 2018

No sir.its a different.

Dossfm0q · Jan 27, 2018

I think you have answered by Mr: Rick Rothstein

https://www.mrexcel.com/forum/excel-questions/1040816-consolidate-data-seperated-comma.html

Code:

[/COLOR]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


[COLOR=#574123]

SRAI31 · Jan 27, 2018

No its complitly different case. Kindly look into question

Dossfm0q · Jan 27, 2018

Sorry
this work for Me
Sub unConsolidateData()

Code:

Sub unConsolidateData()

  Dim B_RngArr As Variant, TXT As String, Dlmtr As String
  Dim LastRow As Long, Rng As Range
  
  Dlmtr = ","
  'or as you type
  'Dlmtr = ", "
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    B_RngArr = Join(Application.Transpose(Range("B1:B2")), Dlmtr)
    Ub = UBound(Split(B_RngArr, Dlmtr)) + 1
    
    For Each Rng In Range("A1:A" & LastRow)
     UA = UBound(Split(Rng.Offset(0, 1).Value, Dlmtr)) + 1
     TXT = TXT & Application.Rept(Rng & Dlmtr, UA)
    
    Next
    
    Range("A1:A" & Ub) = Application.Transpose(Split(B_RngArr, Dlmtr))
    Range("B1:B" & UBound(Split(TXT, Dlmtr))) = Application.Transpose(Split(TXT, Dlmtr))
End Sub

SRAI31 · Jan 27, 2018

Thank you very much .

Rick Rothstein · Jan 27, 2018

Here is another macro that you can consider...

Code:

[table="width: 500"]
[tr]
	[td]Sub UnConsolidateData()
  Dim Combined() As String
  Combined = Split(Join(Application.Transpose(Evaluate("IF({1},A1:A3&""|""&SUBSTITUTE(B1:B3,"","","",""&A1:A3&""|""))")), ","), ",")
  Range("D1").Resize(UBound(Combined) + 1) = Application.Transpose(Combined)
  Columns("D").TextToColumns , xlDelimited, , , False, False, False, False, True, "|"
End Sub[/td]
[/tr]
[/table]

Dossfm0q · Jan 28, 2018

Good Day All
From above How to Fix Array H from Below
Code

Code:

Sub Test()  Dim H As String
  
  Range("F1:G7") = [{"X","A";"X","B";"X","C";"Y","D";"Y","E";"Y","F";"Y","G"}]
  
  H = """X"",""A"";""X"",""B"";""X"",""C"";""Y"",""D"";""Y"",""E"";""Y"",""F"";""Y"",""G"""
  MsgBox H
  Range("H1:I7") = [{H}]
End Sub

Rick Rothstein · Jan 28, 2018

Dossfm0q said:

Good Day All
From above How to Fix Array H from Below
Code

Code:

Sub Test()  Dim H As String
  
  Range("F1:G7") = [{"X","A";"X","B";"X","C";"Y","D";"Y","E";"Y","F";"Y","G"}]
  
  H = """X"",""A"";""X"",""B"";""X"",""C"";""Y"",""D"";""Y"",""E"";""Y"",""F"";""Y"",""G"""
  MsgBox H
  [COLOR="#FF0000"][B]Range("H1:I7") = [{H}][/B][/COLOR]
End Sub

You can only use square brackets if what is inside is a text constant. Since H is a text string , you must concatenate it with the curly braces and use the Evaluate function to evaluate them. Try replacing the red highlighted line of code with this...

Range("H1:I7") = Evaluate("{" & H & "}")

Put comma seperated data into single columb against same vallue

SRAI31

New Member

Excel Facts

pgc01

MrExcel MVP

SRAI31

New Member

Dossfm0q

Banned User

SRAI31

New Member

Dossfm0q

Banned User

SRAI31

New Member

Rick Rothstein

MrExcel MVP

Dossfm0q

Banned User

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Put comma seperated data into single columb against same vallue

New Member

Excel Facts

MrExcel MVP

New Member

Banned User

New Member

Banned User

New Member

MrExcel MVP

Banned User

MrExcel MVP

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