Solved

Replicate data base on single cell

Posted on 2013-01-14
9
228 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
 
LVL 26

Expert Comment

by:redmondb
ID: 38775329
model_un, it works in my one!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 37
DBF to ... Converter 5 29
ADD New Entries 7 15
Error in formula not increasing value 2 6
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

929 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now