create multiple entries from each row (truncating rest of row)

[dougf]

I have a spreadsheet with 4 columns of information, then a column for a primary name, and then many columns of secondary names (with a different number of secondary names for each row).

For rows with more than 1 secondary name, I need to create a new row with a copy of all the columns up to the secondary name for each secondary name in the row.


A row in the spreadsheet looks like this:

data data data data PrimaryName SecondaryName1 SecondaryName2 SecondaryName3... SecondaryNameN

I need this row to be replaced by the following set of rows:

data data data data PrimaryName SecondaryName1

data data data data PrimaryName SecondaryName2

data data data data PrimaryName SecondaryName3
...
data data data data PrimaryName SecondaryNameN



Any help would be greatly appreciated.

 

[dougf]

Here is some possible pseudo code for the algorithm:

int curRow
IF (Cell(FcurRow)!=null)
{
* insert a duplicate of curRow below curRow
* delete all columns after E for curRow
* for the duplicated curRow (which is now curRow+1), shift over columns F through Z and make these columns E through Y
}
curRow++;

 

[dougf]

Are there any specific sources that would help me look up specific VBA functions?

 

[stanleydgromjr]

dougf,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[dougf]

<table style="border-collapse: collapse; width: 415pt;" width="553" border="0" cellpadding="0" cellspacing="0"><col style="width: 51pt;" width="68"> <col style="width: 48pt;" width="64"> <col style="width: 76pt;" width="101"> <col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 51pt;" width="68" align="right" height="20">12/3/2008</td> <td style="width: 48pt;" width="64" align="right">121</td> <td class="xl65" style="width: 76pt;" width="101" align="right">12/3/2008</td> <td style="width: 48pt;" width="64"> Adam</td> <td style="width: 48pt;" width="64">Frank</td> <td style="width: 48pt;" width="64">Phil</td> <td style="width: 48pt;" width="64">Lee</td> <td style="width: 48pt;" width="64">John</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2009</td> <td align="right">223</td> <td class="xl65" align="right">1/3/2009</td> <td> Jan</td> <td>Doe</td> <td>Jill</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2009</td> <td align="right">244</td> <td class="xl65" align="right">1/3/2009
</td> <td> Daniel</td> <td>Cooper</td> <td>Stan</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/4/2009</td> <td align="right">255</td> <td class="xl65" align="right">1/4/2009</td> <td> Joe</td> <td>Stacy</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="5" style="height: 15pt;" height="20">These rows should be replaced by the following rows:</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">12/3/2008</td> <td align="right">121</td> <td class="xl65" align="right">12/3/2008</td> <td> Adam</td> <td>Frank</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">12/3/2008</td> <td align="right">121</td> <td class="xl65" align="right">12/3/2008</td> <td> Adam</td> <td>Phil</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">12/3/2008</td> <td align="right">121</td> <td class="xl65" align="right">12/3/2008</td> <td> Adam</td> <td>Lee</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">12/3/2008</td> <td align="right">121</td> <td class="xl65" align="right">12/3/2008</td> <td> Adam</td> <td>John</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2009</td> <td align="right">223</td> <td class="xl65" align="right">1/3/2009</td> <td> Jan</td> <td>Doe</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2009</td> <td align="right">223</td> <td class="xl65" align="right">1/3/2009</td> <td> Jan</td> <td>Jill</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2009</td> <td align="right">244</td> <td class="xl65" align="right">1/3/2009</td> <td> Daniel</td> <td>Cooper</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2009</td> <td align="right">244</td> <td class="xl65" align="right">1/3/2009</td> <td> Daniel</td> <td>Stan</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/4/2009</td> <td align="right">255</td> <td class="xl65" align="right">1/4/2009</td> <td> Joe</td> <td>Stacy</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[dougf]

For clarification, the date and the name are in two separate columns. The space would not display in the post.

There is an issue with the trust settings on my computer, so I was not able to install Excel Jeanie.

 

[VoG]

Try

Sub MoveData()
Dim LC As Integer, LR As Long, i As Long, j As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Sheets.Add
With ws1
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        LC = .Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 4 To LC
            k = k + 1
            ws2.Range("A" & k & ":C" & k).Value = .Range("A" & i & ":C" & i).Value
            ws2.Range("D" & k).Value = .Cells(i, j).Value
        Next j
    Next i
End With
End Sub

 

[dougf]

Thank you, but there is a minor problem with the code.

I need to preserve the data in column 4 for each row (right now this only preserves the data up to (and including column 3). This code replaces the PrimaryName which I need to keep intact for each SecondaryName row.

 

[VoG]

Try

Sub MoveData()
Dim LC As Integer, LR As Long, i As Long, j As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Sheets.Add
With ws1
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        LC = .Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 5 To LC
            k = k + 1
            ws2.Range("A" & k & ":D" & k).Value = .Range("A" & i & ":D" & i).Value
            ws2.Range("E" & k).Value = .Cells(i, j).Value
        Next j
    Next i
End With
End Sub

 

[dougf]

This worked perfectly. Thank you very much for your help!