Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Replicate data base on single cell

Posted on 2013-01-14
9
Medium Priority
?
235 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 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

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

Accepted Solution

by:
gowflow earned 1200 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 400 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

916 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