Creating New Lines from original

[Balthasar G]

Have an exisiting line on a worksheet that has a cell that identifies that it is a combination of lines

Know the logic behind the combine ie if Combine should be individual line for Dept X, Dept Y and Dept Z

Want to know how I can extract this data onto another worksheet.

Might be best to show an example:-

Example Line on exising worksheet
Date Account Number Combine

Output required

Date Account Number Dept X
Date Account Number Dept Y
Date Account Number Dept Z

Any ideas on the code to do this.

[Otto Moehrbach]

Is it always the same X, Y, and Z? What if the word Combine isn't there?
What do want as the output then? HTH Otto
"Balthasar G" <[email protected]>
wrote in message
news:[email protected]...
>
> Have an exisiting line on a worksheet that has a cell that identifies
> that it is a combination of lines
>
> Know the logic behind the combine ie if Combine should be individual
> line for Dept X, Dept Y and Dept Z
>
> Want to know how I can extract this data onto another worksheet.
>
> Might be best to show an example:-
>
> Example Line on exising worksheet
> Date Account Number Combine
>
> Output required
>
> Date Account Number Dept X
> Date Account Number Dept Y
> Date Account Number Dept Z
>
> Any ideas on the code to do this.
>
>
> --
> Balthasar G
> ------------------------------------------------------------------------
> Balthasar G's Profile:
> http://www.excelforum.com/member.php...o&userid=37264
> View this thread: http://www.excelforum.com/showthread...hreadid=572125
>

[Balthasar G]

Hi
Its always the same X Y Z
If the word combine is not there it should just output the line as it stands to the new worksheet.
If the word combine is there it should produce a line for each of the dept on the new worksheet.

Hope this helps.

[Balthasar G]

Input Worksheet

Date, Account Number, Combine
Date, Account Number,


Output Worksheet

Date Account Number, Dep X
Date Account Number, Dep Y
Date Account Number, Dep Z
Date Account Number,

[Otto Moehrbach]

Balthasar
This macro will do what you want. I assumed that the original data is
in Column A starting in row 2 with row 1 being headers. Note that the sheet
with the original data must be the active sheet (the sheet on the screen).
I chose a sheet name of "Two" for the destination sheet. Sheet 2 also has
headers in row 1. This sheet name ("Two") is written in the macro so you
must change that name in the macro to your actual sheet name. HTH Otto
Sub DoData()
Dim RngColA As Range
Dim i As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A2", .Range("A" &
Rows.Count).End(xlUp).Offset(1))
End With
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If i.Offset(, 2).Value = "Combine" Then
i.Resize(, 2).Copy Dest.Resize(3)
Dest.Offset(, 2).Value = "Dep X"
Dest.Offset(1, 2).Value = "Dep Y"
Dest.Offset(2, 2).Value = "Dep Z"
Set Dest = Dest.Offset(3)
Else
i.Resize(, 2).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub
"Balthasar G" <[email protected]>
wrote in message
news:[email protected]...
>
> Input Worksheet
>
> Date, Account Number, Combine
> Date, Account Number,
>
>
> Output Worksheet
>
> Date Account Number, Dep X
> Date Account Number, Dep Y
> Date Account Number, Dep Z
> Date Account Number,
>
>
> --
> Balthasar G
> ------------------------------------------------------------------------
> Balthasar G's Profile:
> http://www.excelforum.com/member.php...o&userid=37264
> View this thread: http://www.excelforum.com/showthread...hreadid=572125
>

[Otto Moehrbach]

I don't see my reply so I'll send it again. Otto
Sub DoData()
Dim RngColA As Range
Dim i As Range
Dim Dest As Range
With Sheets("Two")
Set Dest = .Range("A2", .Range("A" &
Rows.Count).End(xlUp).Offset(1))
End With
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If i.Offset(, 2).Value = "Combine" Then
i.Resize(, 2).Copy Dest.Resize(3)
Dest.Offset(, 2).Value = "Dep X"
Dest.Offset(1, 2).Value = "Dep Y"
Dest.Offset(2, 2).Value = "Dep Z"
Set Dest = Dest.Offset(3)
Else
i.Resize(, 2).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub
"Balthasar G" <[email protected]>
wrote in message
news:[email protected]...
>
> Input Worksheet
>
> Date, Account Number, Combine
> Date, Account Number,
>
>
> Output Worksheet
>
> Date Account Number, Dep X
> Date Account Number, Dep Y
> Date Account Number, Dep Z
> Date Account Number,
>
>
> --
> Balthasar G
> ------------------------------------------------------------------------
> Balthasar G's Profile:
> http://www.excelforum.com/member.php...o&userid=37264
> View this thread: http://www.excelforum.com/showthread...hreadid=572125
>

[Balthasar G]

Have tried this on my test model and it works great, off now to apply it to the main spreadsheet though I should not see any problems with it working.
Just like to say thanks from a newbie vba.

[Balthasar G]

Got one more issue.
Having now looked at the spreadsheet I need to perfom this on it has 42 columns with the "Combine" field in column 10.

Having real difficulty changing the code to cope with this.

Wonder if you could assist.

Thanks

[Otto Moehrbach]

Balthasar
I just don't know what you want to do. What do you want done with the
42 columns? I take it that you still have the situation where some of the
rows have "Combine" and some don't. Give me some examples like you did
before. HTH Otto
"Balthasar G" <[email protected]>
wrote in message
news:[email protected]...
>
> Got one more issue.
> Having now looked at the spreadsheet I need to perfom this on it has 42
> columns with the "Combine" field in column 10.
>
> Having real difficulty changing the code to cope with this.
>
> Wonder if you could assist.
>
> Thanks
>
>
> --
> Balthasar G
> ------------------------------------------------------------------------
> Balthasar G's Profile:
> http://www.excelforum.com/member.php...o&userid=37264
> View this thread: http://www.excelforum.com/showthread...hreadid=572125
>

[Balthasar G]

Otto
Have spent most of today working on this and have managed to get it to work.
Thanks again for your assistance and most of all for your patience.
Cheers
Bal G