Separating data in a row into new rows

M

MortenPetterson

Hi there,

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

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

Many thanks,

Marti

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

plinius

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.
 
R

Ron Rosenfeld

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
=======================================
 
M

MortenPetterson

Plinius and Ron Rosenfeld,

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

Marti

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top