Separating data in a row into new rows
 

Hi there,

I have a database of client records which holds information that I need to create individual records for. I have exported it to excel, which gives all the client info in one row but I need to separate this out by "plan" so that the general client info is copied down to each new record - does anyone have any idea as to how I go about this? The number of plans for each client varies.

The attachment shows an example of what I currently have and then what I am trying to achieve - I hope it clarifies the situation.

Many thanks,

Martin

Separating data in a row into new rows
 

Il 25/07/2012 18:16, MortenPetterson ha scritto:
Hi there,

I have a database of client records which holds information that I need
to create individual records for. I have exported it to excel, which
gives all the client info in one row but I need to separate this out by
"plan" so that the general client info is copied down to each new record
- does anyone have any idea as to how I go about this? The number of
plans for each client varies.

The attachment shows an example of what I currently have and then what I
am trying to achieve - I hope it clarifies the situation.

Many thanks,

Martin


+-------------------------------------------------------------------+
|Filename: Creating plan records.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=495|
+-------------------------------------------------------------------+



Without using VBA, I suppose data are in range A1:K31

In L1 insert 1
In L2 insert =L1+(ROW()M1+1)
In M2 insert =COUNTA(INDIRECT("$D$2:$K"&L2))
In N2 insert =INDEX($A$1:$K$31,$L2,COLUMN(A1))
In O2 insert =INDEX($A$1:$K$31,$L2,COLUMN(B1))
In P2 insert =INDEX($A$1:$K$31,$L2,COLUMN(C1))
In Q2 insert =INDEX($A$1:$K$31,$L2,COLUMN(C1)+COUNTIF($L$2:$L2, $L2))

Copy down range L2:Q2 as it need.

Hi,
E.

Separating data in a row into new rows
 

On Wed, 25 Jul 2012 16:16:32 +0000, MortenPetterson wrote:


Hi there,

I have a database of client records which holds information that I need
to create individual records for. I have exported it to excel, which
gives all the client info in one row but I need to separate this out by
"plan" so that the general client info is copied down to each new record
- does anyone have any idea as to how I go about this? The number of
plans for each client varies.

The attachment shows an example of what I currently have and then what I
am trying to achieve - I hope it clarifies the situation.

Many thanks,

Martin


+-------------------------------------------------------------------+
|Filename: Creating plan records.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=495|
+-------------------------------------------------------------------+

Given the database in the format you picture (Plans limited to a maximum of 3), the following Macro should work. It will place the results on a second worksheet. You should be able to fine tune depending on any unstated requirements.
Please note that the source data is hard coded to be on "Sheet1" and the results to be placed on "Sheet2". Again you can easily modify this.

Note that the macro assumes there is nothing of value below the imported table.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

=================================
Option Explicit
Sub MakeMultipleRows()
Dim rSrc As Range, c As Range, rw As Range
Dim rDest As Range
Dim vRes() As Variant
Dim i As Long, j As Long
With Worksheets("Sheet1")
Set rSrc = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set rSrc = rSrc.Resize(columnsize:=6)
Set rDest = Worksheets("Sheet2").Range("A1")

ReDim vRes(1 To WorksheetFunction.CountA(Range(rSrc(1, 4), rSrc(rSrc.Rows.Count, 6))), 1 To 4)

For Each rw In rSrc.Rows
For Each c In Range(rw.Cells(columnindex:=4), rw.Cells(columnindex:=6))
If Len(c.Text) 0 Then
i = i + 1
For j = 1 To 3
vRes(i, j) = rw.Cells(columnindex:=j)
Next j
vRes(i, 4) = c.Text
End If
Next c
Next rw

Set rDest = rDest.Resize(rowsize:=UBound(vRes, 1), columnsize:=4)
rDest.EntireColumn.ClearContents
rDest = vRes
rDest.EntireColumn.AutoFit

End Sub
=======================================

Plinius and Ron Rosenfeld,

Both of those work and I can tweek to fit the purpose. Greatly appreciated and many thanks.

Martin