Excel Help : How to get atomic values in excel cell.

[fashxfreak]

I have data in which column 'C' have cells which contain multiple values separated by (,).Now I want to get atomic values in all cells of column 'C'. i.e. that for a cell when there is more than one value; separated by (,) it should be copied in the next row with all the other columns of that row copied with the value we want to separate, in the next row...
In simple I want my data to be in 1NF.

[LastMitch]

I have data in which column 'C' have cells which contain multiple values separated by (,).Now I want to get atomic values in all cells of column 'C'. i.e. that for a cell when there is more than one value; separated by (,) it should be copied in the next row with all the other columns of that row copied with the value we want to separate, in the next row...In simple I want my data to be in 1NF.

You need to post a code snippet or a image of what you are talking about.

You can read this

http://office.microsoft.com/en-us/excel-help/relationships-between-tables-in-a-data-model-HA102836654.aspx

and also read this:

http://office.microsoft.com/en-us/excel-help/create-a-pivottable-to-analyze-data-in-multiple-tables-HA102897373.aspx

If that's not what you are talking about then you need to post a code snippet or an image and explain it more clearly.

[Stuugie]

I'm not saying this because I want to push people away from this site but this can more than likely be solved at an Excel site. You'll be able to post a workbook where people can work with what you have. Am I able to provide a link to a really good MS Office forum?

[fashxfreak]

Thx @Stuugie for your help.
I have attached the image of what sort of data i have & what i actually want to do. it is just a portion of data & i sorted it manually to show that what i want.If you think there is a better place for this to be worked out , I'll be happy to know...
Regards...

[Stuugie]

Hi fashxfreak, try the following code, it should be close but it's not fully tested

Option Explicit
Sub SplitCells()

    Dim wS As Worksheet
    Dim oC As Range
    Dim cArry() As String
    Dim x As Long, y As Long, z As Long

    Set wS = Worksheets("Sheet1")
    For Each oC In wS.Range(wS.Cells(2, 2), wS.Cells(wS.Rows.Count, 2).End(xlUp))
        If oC.Value <> "NIL" Then
            cArry() = Split(oC.Value, ",")
            y = UBound(cArry)
            For z = 1 To y
                oC.Offset(z, 0).EntireRow.Insert
            Next z
            For x = LBound(cArry) To UBound(cArry)
                If x = UBound(cArry) Then Exit For
                oC.Value = cArry(x)
                oC.Offset(x + 1, 0).Value = cArry(x + 1)
                oC.Offset(x + 1, -1).Value = oC.Offset(0, -1).Value
            Next x
        End If
    Next oC
End Sub

[Stuugie]

I found an error in my last code example and revised it

Option Explicit
Sub SplitCells()

    Dim wS As Worksheet
    Dim oC As Range
    Dim cArry() As String
    Dim x As Long, y As Long, z As Long

    Set wS = Worksheets("Sheet1")
    For Each oC In wS.Range(wS.Cells(2, 2), wS.Cells(wS.Rows.Count, 2).End(xlUp))
        If oC.Value <> "NIL" Then
            cArry() = Split(oC.Value, ",")
            y = UBound(cArry)
            For z = 1 To y
                oC.Offset(z, 0).EntireRow.Insert
            Next z
            For x = LBound(cArry) To UBound(cArry)
                If x > UBound(cArry) Then Exit For
                If x = 0 Then
                    oC.Value = cArry(x)
                ElseIf x = 1 Then
                    oC.Offset(x, 0).Value = cArry(x)
                    oC.Offset(x, -1).Value = oC.Offset(0, -1).Value
                ElseIf x = 2 Then
                    oC.Offset(x, 0).Value = cArry(x)
                    oC.Offset(x, -1).Value = oC.Offset(0, -1).Value
                End If
            Next x
        End If
    Next oC
End Sub

Look at the If x = 0 then and specifically the last ElseIf. If you there will be more than 3 comma separated values you'll have to adjust this part so for example if there are 4 comma separated values you should add

ElseIF x = 3 then
    oC.Offset(x,0).Value = cArry(x)
    oC.Offset(x, -1).Value = oC.offset(0,-1).Value
End If

Let me know how this goes for you.

[fashxfreak]

Stuugie that image was just an example of what i want - here is my actual data in the attachment.
i dont only want to separate the values after (,) but also want that the new row which is generated from the values we have separated must have all the same attributes as the row from which it is separated. :P hope u can understand what im trying to say... please see the attachment for my actual data & image.
thx...

[Stuugie]

If you upload the Excel file I can do more for you.

[fashxfreak]

here is the original data file...Please have a look.
If you are still having problems plz send me your email I'll mail you the file, cuz here on this forum file can't be attached...
thx

http://www.fileflyer.com/view/EFpLRCj
OR
http://www.speedyshare.com/q53u2/Excel-Data-Sheet.csv