Splitting multiple data in cell while copying rest of row

Question

I am trying to normalize a Pokemon DB in to 1NF, however, I am unsure how to do this in Excel.

I am wanting to take the multiple data entries in 'Abilities' and split them and duplicate the row.

Original data

+----------------+-----------+---------------+-----------------------------+
| pokedex_number |   name    | classfication |          abilities          |
+----------------+-----------+---------------+-----------------------------+
|              1 | Bulbasaur | Seed Pokemon  | ['Overgrow', 'Chlorophyll'] |
|              2 | Ivysaur   | Seed Pokemon  | ['Overgrow', 'Chlorophyll'] |
|              3 | Venusaur  | Seed Pokemon  | ['Overgrow', 'Chlorophyll'] |
+----------------+-----------+---------------+-----------------------------+

Desired data

+----------------+-----------+---------------+-------------+
| pokedex_number |   name    | classfication |  abilities  |
+----------------+-----------+---------------+-------------+
|              1 | Bulbasaur | Seed Pokemon  | Overgrow    |
|              1 | Bulbasaur | Seed Pokemon  | Chlorophyll |
|              2 | Ivysaur   | Seed Pokemon  | Overgrow    |
|              2 | Ivysaur   | Seed Pokemon  | Chlorophyll |
|              3 | Venusaur  | Seed Pokemon  | Overgrow    |
|              3 | Venusaur  | Seed Pokemon  | Chlorophyll |
+----------------+-----------+---------------+-------------+

This is not clear. What exactly are you asking for? Code? To do what exactly--what input to what output? Do you just want to know if your example is an example of normalization to 1NF? Or what? PS ["1NF" has no single meaning.](https://stackoverflow.com/a/40640962/3404097) What is your definition of "1NF"? Are you following some textbook or class or what? Why do you want that? If you want to write queries that use a column for an ability then this is the sort of "1NF" transformation you should do. — philipxy, May 22 '19 at 06:32

score 0 · Answer 1 · answered May 22 '19 at 09:32

You could try:

Option Explicit
Dim strResults As String

Sub test()

    Dim varSplit As Variant
    Dim LastRow As Long, i As Long, Counter As Long, y As Long, CounterArr As Long
    Dim pokedex_number As String, name As String, classfication As String

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = LastRow To 2 Step -1

            Counter = Len(.Range("D" & i).Value) - Len(Replace(.Range("D" & i).Value, ",", ""))

            If Counter > 0 Then

                Call Module1.Clean(.Range("D" & i).Value)

                varSplit = Split(strResults, ",")

                pokedex_number = .Range("A" & i).Value
                name = .Range("B" & i).Value
                classfication = .Range("C" & i).Value

                .Rows(i + 1 & ":" & i + Counter).Insert

                CounterArr = 0

                For y = i To Counter + i

                    .Range("A" & y).Value = pokedex_number
                    .Range("B" & y).Value = name
                    .Range("C" & y).Value = classfication
                    .Range("D" & y).Value = varSplit(CounterArr)

                    CounterArr = CounterArr + 1

                Next y

            End If

        Next i

    End With

End Sub

Sub Clean(ByVal str As String)

    strResults = Replace(Replace(Replace(Replace(str, "]", ""), "[", ""), " ", ""), "'", "")

End Sub

score 0 · Answer 2 · answered May 22 '19 at 17:29

Use Power Query (aka Get&Transform) to split the abilities column into rows Then remove the extraneous characters []'

This can all be done from the PQ GUI.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"[","",Replacer.ReplaceText,{"abilities"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","'","",Replacer.ReplaceText,{"abilities"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","]","",Replacer.ReplaceText,{"abilities"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"abilities", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "abilities"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"abilities", type text}})
in
    #"Changed Type"

Results

Ron! Dead set legend. So easy with the Power Query split function. Thanks so much for your help. — PlantPot, May 23 '19 at 23:05

Splitting multiple data in cell while copying rest of row

2 Answers2

M-Code

Results