Solved

Replicate data base on single cell

Posted on 2013-01-14
9
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 26

Expert Comment

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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

623 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