Solved

Replicate data base on single cell

Posted on 2013-01-14
9
230 Views
Last Modified: 2013-01-14
Hello All.

I have a dataset with eleven columns. The last column/cell contains data (eg. '1, 2, 3') that needs to be (1) separated and (2) used to replicate the previous data.

Columns 1-10 will need to be replicated with each new row based on the 'separated' last cell. So if the last cell has two+ variables then the variable will be separated and the other data replicated.

So you go from:
A         B
xxx      1, 2, 3


To:
A         B
xxx      1
xxx      2
xxx      3

See dataset attached.

Thanks,

Fernando
refusal-charges-test-data.xlsx
0
Comment
Question by:model_un
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38775227
Maybe try the attached..
refusal-charges-test-data.xlsm
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 100 total points
ID: 38775288
Hi, model_un.

Please see attached (which does all entries including the last one). The code is...
Option Explicit

Sub Split_Comma()
Dim i As Long
Dim j As Long
Dim k As Long
Dim xLast_Row As Long
Dim xCell As Range
Dim xArray  As Variant
Dim xOld As Worksheet
Dim xNew As Worksheet
        
Set xOld = ThisWorkbook.Sheets("Sheet1")

xLast_Row = xOld.[A1].SpecialCells(xlLastCell).Row
If xLast_Row < 2 Then
    MsgBox ("No data found - run cancelled.")
    Exit Sub
End If

Application.ScreenUpdating = False
        
    Set xNew = Sheets.Add
    
    For i = 1 To xLast_Row
        xArray = Split(xOld.Cells(i, 11), ",")
        If UBound(xArray) > -1 Then
            For j = 0 To UBound(xArray)
                k = k + 1
                xOld.Rows(i).Copy Destination:=xNew.Range("A" & k)
                xNew.Cells(k, 11) = xArray(j)
            Next
        Else
            k = k + 1
            xOld.Rows(i).Copy Destination:=xNew.Range("A" & k)
        End If
    Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
refusal-charges-test-data-V2.xlsm
0
 

Author Comment

by:model_un
ID: 38775318
Thanks The_Barman.

Question... when I ran your macro again using the test data... I notice that it is not returning the last line (the only one with a single entry).

Comments?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Expert Comment

by:redmondb
ID: 38775329
model_un, it works in my one!
0
 
LVL 29

Accepted Solution

by:
gowflow earned 300 total points
ID: 38775342
try this. Make sure macroes are enabled and run the button in Sheet1 and it will create a brand new sheet with date labled.
gowflow.
refusal-charges-test-data.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38775391
the last line is my bad needed to add one to output...

Sub Run_Macro()

Dim Arr, Arr2
Dim ArrOut()

Z = 0
Arr = Sheets("Sheet1").UsedRange.Value
For x = 2 To UBound(Arr)
Arr2 = Split(Arr(x, 11), ",")
    For y = 0 To UBound(Arr2)
        Z = Z + 1
        ReDim Preserve ArrOut(1 To 11, 1 To Z)
        For i = 1 To 10
            ArrOut(i, Z) = Arr(x, i)
        Next i
        ArrOut(11, Z) = Arr2(y)
    Next y
Next x

Sheets("sheet2").Range("A2:K" & UBound(ArrOut, 2) + 1).Value = Application.Transpose(ArrOut)

End Sub

Open in new window


Brian, over large data sets you will find that the cell level changes slow the whole code down considerably... I just made the schoolboy error of my output range being one row too small. (doh!)
refusal-charges-test-data.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38775498
The_Barman,

Thanks, I'm very aware of... both of those points!

Unless a macro has a significant time-impact on the user, I tend to lean to the side of simplicity over speed. I'm very reluctant to give a user a solution which is beyond their capability to support.

Regards,
Brian.
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 100 total points
ID: 38775534
And I have seen that there is a date error in the code.. good old VBA dates... attached is code which corrects the issue...

Sub Run_Macro()

Dim Arr, Arr2
Dim ArrOut()

Z = 0
Arr = Sheets("Sheet1").UsedRange.Value
For x = 2 To UBound(Arr)
Arr2 = Split(Arr(x, 11), ",")
    For y = 0 To UBound(Arr2)
        Z = Z + 1
        ReDim Preserve ArrOut(1 To 11, 1 To Z)
        For i = 1 To 9
            ArrOut(i, Z) = Arr(x, i)
        Next i
        ArrOut(10, Z) = Format(Arr(x, 10), "dd-mmm-yyyy")
        ArrOut(11, Z) = Arr2(y)
    Next y
Next x

Sheets("sheet2").Range("A2:K" & UBound(ArrOut, 2) + 1).Value = Application.Transpose(ArrOut)

End Sub

Open in new window

refusal-charges-test-data.xlsm
0
 

Author Closing Comment

by:model_un
ID: 38776275
Thank you all.

In the end GowFlow's solution worked without freezing my system.

Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question