• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to replicate data in column B with column A [code snippet required]

Mr.Karr

Member
With the sample file attached, there are 2 columns. Column A has ID that is unique. Column B has comma separate values.
Wherever there are multiple values in columnB, ID in columnA has to be replicated to take values from columnB and create multiple rows accordingly.

IDNamesIDNames
123
Pencil, Paper, Blue Pen, Box
123Pencil
123Paper
123Blue Pen
123Box

Sample file attached. Thanks in advance.
 

Attachments

  • sample file.xlsx
    10.1 KB · Views: 3
According to your attachment a VBA beginner starter demonstration :​
Code:
Sub Demo1()
    Dim V, R&, L&, S$()
        [E1].CurrentRegion.Offset(1).Clear
        Application.ScreenUpdating = False
        V = [A1].CurrentRegion.Value2
        R = 2
    For L = 2 To UBound(V)
            S = Split(V(L, 2), ", ")
        With Cells(R, 5).Resize(UBound(S) + 1)
            .Columns(1) = V(L, 1)
            .Columns(2) = Application.Transpose(S)
        End With
            R = R + UBound(S) + 1
    Next
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Using Power Query/Get and Transform found on the Data Tab. Here is the Mcode to split the cells into rows.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Names", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Names", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Names"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Names", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Names", Text.Trim, type text}})
in
    #"Trimmed Text"

Data Range
A
B
1
ID​
Names​
2
123​
Pencil​
3
123​
Paper​
4
123​
Blue Pen​
5
123​
Eraser​
6
123​
Keyboard​
7
547​
Apple Gala​
8
547​
Orange​
9
547​
Banana​

File attached for your review.
 

Attachments

  • sample file.xlsx
    26.3 KB · Views: 5
Back
Top