Multiple marriages

InaCell

InaCell

Board Regular
Joined
Feb 2, 2010
Messages
189
Hi Glenn<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Data<o:p></o:p>
HTML: 
<html><head><title>Excel Jeanie HTML</title></head><body>[RANGE=cls:xl2bb-100][XR][XH=cs:9]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]1[/XH][XD=h:c|fw:b]Male SURNAME[/XD][XD=h:r]1[/XD][XD][/XD][XD=h:c|fw:b]Son SURNAME[/XD][XD=h:r]14[/XD][XD][/XD][XD=h:c|fw:b|bc:ff0000]Jane SURNAME[/XD][XD=h:r]52[/XD][/XR][XR][XH]2[/XH][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][/XR][XR][XH]3[/XH][XD=h:c|fs:i]d.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]m.[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]4[/XH][XD=h:c|fs:i]bur.[/XD][XD][/XD][XD][/XD][XD=h:c|fw:b|bc:ff0000]Jane GIRL[/XD][XD=h:r]15[/XD][XD][/XD][XD=h:c|fw:b]James SURNAME[/XD][XD=h:r]53[/XD][/XR][XR][XH]5[/XH][XD=h:c|fs:i]m.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][/XR][XR][XH]6[/XH][XD=h:c|fw:b|bc:ff0000]Female MAIDEN[/XD][XD=h:r]2[/XD][XD][/XD][XD=h:c|fs:i]d.[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]7[/XH][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]bur.[/XD][XD][/XD][XD][/XD][XD=h:c|fw:b|bc:ff0000]Julia SURNAME[/XD][XD=h:r]54[/XD][/XR][XR][XH]8[/XH][XD=h:c|fs:i]d.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]m.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=h:c|fs:i]bur.[/XD][XD][/XD][XD][/XD][XD=h:c|fw:b|bc:ff0000]Anne JONES[/XD][XD=h:r]16[/XD][XD][/XD][XD=fs:i][/XD][XD][/XD][/XR][XR][XH]10[/XH][XD=h:c|fs:i]m.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=fs:i][/XD][XD][/XD][/XR][XR][XH]11[/XH][XD=h:c|fw:b|bc:ff0000]Female SMITH[/XD][XD=h:r]3[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]12[/XH][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fw:b|bc:ff0000]Karen SURNAME[/XD][XD=h:r]17[/XD][XD][/XD][XD=h:c|fw:b]Tom DOE[/XD][XD=h:r]55[/XD][/XR][XR][XH]13[/XH][XD=h:c|fs:i]d.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][/XR][XR][XH]14[/XH][XD=h:c|fs:i]bur.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]m.[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]15[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c|fw:b]John DOE[/XD][XD=h:r]18[/XD][XD][/XD][XD=h:c|fw:b|bc:ff0000]Amy DOE[/XD][XD=h:r]56[/XD][/XR][XR][XH]16[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][XD][/XD][XD=h:c|fs:i]b.[/XD][XD][/XD][/XR][XR][XH=cs:9][RANGE][XR][XD]Testing[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
 
</body></html>
<o:p></o:p>
Expected result<o:p></o:p>
HTML: 
<html><head><title>Excel Jeanie HTML</title></head><body>[RANGE=cls:xl2bb-100][XR][XH=cs:7]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][/XR][XR][XH]1[/XH][XD=h:l]ID[/XD][XD=h:l]Name[/XD][XD=h:l]Sex[/XD][XD=h:l]Father[/XD][XD=h:l]Mother[/XD][XD=h:l]Spouse[/XD][/XR][XR][XH]2[/XH][XD=h:r]1[/XD][XD=h:l]Male SURNAME[/XD][XD=h:l]M[/XD][XD][/XD][XD][/XD][XD=h:r]2[/XD][/XR][XR][XH]3[/XH][XD=h:r]1[/XD][XD=h:l]Male SURNAME[/XD][XD=h:l]M[/XD][XD][/XD][XD][/XD][XD=h:r]3[/XD][/XR][XR][XH]4[/XH][XD=h:r]2[/XD][XD=h:l]Female MAIDEN[/XD][XD=h:l]F[/XD][XD][/XD][XD][/XD][XD=h:r]1[/XD][/XR][XR][XH]5[/XH][XD=h:r]3[/XD][XD=h:l]Female SMITH[/XD][XD=h:l]F[/XD][XD][/XD][XD][/XD][XD=h:r]1[/XD][/XR][XR][XH]6[/XH][XD=h:r]14[/XD][XD=h:l]Son SURNAME[/XD][XD=h:l]M[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]15[/XD][/XR][XR][XH]7[/XH][XD=h:r]14[/XD][XD=h:l]Son SURNAME[/XD][XD=h:l]M[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]16[/XD][/XR][XR][XH]8[/XH][XD=h:r]15[/XD][XD=h:l]Jane GIRL[/XD][XD=h:l]F[/XD][XD][/XD][XD][/XD][XD=h:r]14[/XD][/XR][XR][XH]9[/XH][XD=h:r]16[/XD][XD=h:l]Anne JONES[/XD][XD=h:l]F[/XD][XD][/XD][XD][/XD][XD=h:r]14[/XD][/XR][XR][XH]10[/XH][XD=h:r]17[/XD][XD=h:l]Karen SURNAME[/XD][XD=h:l]F[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]17[/XD][/XR][XR][XH]11[/XH][XD=h:r]18[/XD][XD=h:l]John DOE[/XD][XD=h:l]M[/XD][XD][/XD][XD][/XD][XD=h:r]16[/XD][/XR][XR][XH]12[/XH][XD=h:r]52[/XD][XD=h:l]Jane SURNAME[/XD][XD=h:l]F[/XD][XD=h:r]14[/XD][XD=h:r]15[/XD][XD][/XD][/XR][XR][XH]13[/XH][XD=h:r]53[/XD][XD=h:l]James SURNAME[/XD][XD=h:l]M[/XD][XD=h:r]14[/XD][XD=h:r]15[/XD][XD][/XD][/XR][XR][XH]14[/XH][XD=h:r]54[/XD][XD=h:l]Julia SURNAME[/XD][XD=h:l]F[/XD][XD=h:r]14[/XD][XD=h:r]15[/XD][XD][/XD][/XR][XR][XH]15[/XH][XD=h:r]55[/XD][XD=h:l]Tom DOE[/XD][XD=h:l]M[/XD][XD=h:r]17[/XD][XD=h:r]16[/XD][XD][/XD][/XR][XR][XH]16[/XH][XD=h:r]56[/XD][XD=h:l]Amy DOE[/XD][XD=h:l]F[/XD][XD=h:r]17[/XD][XD=h:r]16[/XD][XD][/XD][/XR][XR][XH=cs:7][RANGE][XR][XD]List[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
</body></html>
<o:p></o:p>
This was my sample code to get the first 3 columns how I wanted it. But doesn't get the multiple marriage instance.<o:p></o:p>
Code: 
Sub TEST()
Dim x As Long, a As Long, b As Long, c As Long, d As Long
Dim e As Long, f As Long
'x = sets last used row for range
'a = source Col, b = source Row
'c = dest Row, d = dest Col
'e = source Col, f = source Row (Source of Individual Id #)
Application.ScreenUpdating = False ' speed up code for macro
Application.Calculation = xlCalculationManual ' speed up code for macro
c = 1<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For a = 1 To 7 Step 3 'selects columns A D G
c = c 'begins Row 1
d = 1 'begins in Col A
x = Sheets("Testing").Cells(Rows.Count, a).End(xlUp).Row ' sets last row of source range
For b = 1 To x 'source range starts at row 1
If Sheets("Testing").Cells(b, a) <> "" Then ' excludes blanks in source range
If Sheets("Testing").Cells(b, a).Font.Bold Then 'if bold text
c = c + 1 'dest Row +1 (next row)
d = 1 'Begins in dest Col A
e = a + 1
f = b
Sheets("List").Cells(c, d) = Cells(f, e)
d = d + 1 'dest next Col
Sheets("List").Cells(c, d) = Cells(b, a)
'Copies Bold data from source to destination
d = d + 1 'dest next Col
'================================
'This section puts in Sex (M/F)
If Sheets("Testing").Cells(b, a).Interior.ColorIndex > 2 Then
Sheets("List").Cells(c, d).Value = "F"
Else
Sheets("List").Cells(c, d).Value = "M"
'Copies Sex data from source to destination
End If
'================================
'This section puts in parent Id #

'================================
'This section puts in spouse Id #
Else
'================================
End If
End If
Next b 'goes to next Row in source sheet
Next a 'goes to next Col in source sheet
Application.Calculation = xlCalculationAutomatic ' set back to normal
Application.ScreenUpdating = True ' set back to normal
MsgBox "Complete" 'Alert, macro finished
End Sub
<o:p></o:p>
Thanks again<o:p></o:p>
<o:p> </o:p>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
InaCell,

You want to paste the informaiton from Excel Jeanie into the same area where your type your request.

Try again, and do not wrap Excel Jeanie in CDDE tabs.
 
Upvote 0
Hi Glenn<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Data<o:p></o:p>
<o:p></o:p>
<title>Excel Jeanie HTML</< font>title></< font>head><body><b>Testing</< font>b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:180px;" /><col style="width:19px;" /><col style="width:19px;" /><col style="width:180px;" /><col style="width:19px;" /><col style="width:19px;" /><col style="width:180px;" /><col style="width:19px;" /></< font>colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </< font>td><td >A</< font>td><td >B</< font>td><td >C</< font>td><td >D</< font>td><td >E</< font>td><td >F</< font>td><td >G</< font>td><td >H</< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</< font>td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Male SURNAME</< font>td><td style="text-align:right; ">1</< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-width:1px; border-color:#000000; ">Son SURNAME</< font>td><td style="text-align:right; ">14</< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-width:1px; border-color:#000000; ">Jane SURNAME</< font>td><td style="text-align:right; ">52</< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">d.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">m.</< font>td><td > </< font>td><td > </< font>td><td style=" border-bottom-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">bur.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">Jane GIRL</< font>td><td style="text-align:right; ">15</< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">James SURNAME</< font>td><td style="text-align:right; ">53</< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">m.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Female MAIDEN</< font>td><td style="text-align:right; ">2</< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">d.</< font>td><td > </< font>td><td > </< font>td><td style=" border-bottom-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">bur.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">Julia SURNAME</< font>td><td style="text-align:right; ">54</< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">d.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">m.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">bur.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">Anne JONES</< font>td><td style="text-align:right; ">16</< font>td><td > </< font>td><td style="font-style:italic; "> </< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">m.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td > </< font>td><td style="font-style:italic; "> </< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Female SMITH</< font>td><td style="text-align:right; ">3</< font>td><td > </< font>td><td style=" border-bottom-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td > </< font>td><td > </< font>td><td style=" border-bottom-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">Karen SURNAME</< font>td><td style="text-align:right; ">17</< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">Tom DOE</< font>td><td style="text-align:right; ">55</< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">d.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">bur.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">m.</< font>td><td > </< font>td><td > </< font>td><td style=" border-bottom-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td > </< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</< font>td><td > </< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">John DOE</< font>td><td style="text-align:right; ">18</< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="background-color:#ff0000; font-weight:bold; text-align:center; border-right-style:solid; border-width:1px; border-color:#000000; ">Amy DOE</< font>td><td style="text-align:right; ">56</< font>td></< font>tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</< font>td><td > </< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td><td style=" border-right-style:solid; border-width:1px; border-color:#000000; "> </< font>td><td style="font-style:italic; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">b.</< font>td><td > </< font>td></< font>tr></< font>table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </< font>span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4</</< font>a><o:p></o:p>
<o:p></o:p>
</< font></< font>
<o:p></o:p>
<o:p></o:p>

Expected result<o:p></o:p>
<title>Excel Jeanie HTML</< font>title></< font>head><body><b>List</< font>b><br/><br/><tableborder="0"cellspacing="0"cellpadding="0"style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><colstyle="font-weight:bold; width:30px; "/><colstyle="width:21px;"/><colstyle="width:109px;"/><colstyle="width:29px;"/><colstyle="width:47px;"/><colstyle="width:53px;"/><colstyle="width:64px;"/></< font>colgroup><trstyle="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </< font>td><td>A</< font>td><td>B</< font>td><td>C</< font>td><td>D</< font>td><td>E</< font>td><td>F</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">1</< font>td><td>ID</< font>td><td>Name</< font>td><td>Sex</< font>td><td>Father</< font>td><td>Mother</< font>td><td>Spouse</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">2</< font>td><tdstyle="text-align:right; ">1</< font>td><tdstyle="text-align:left; ">Male SURNAME</< font>td><tdstyle="text-align:left; ">M</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">2</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">3</< font>td><tdstyle="text-align:right; ">1</< font>td><tdstyle="text-align:left; ">Male SURNAME</< font>td><tdstyle="text-align:left; ">M</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">3</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">4</< font>td><tdstyle="text-align:right; ">2</< font>td><tdstyle="text-align:left; ">Female MAIDEN</< font>td><tdstyle="text-align:left; ">F</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">1</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">5</< font>td><tdstyle="text-align:right; ">3</< font>td><tdstyle="text-align:left; ">Female SMITH</< font>td><tdstyle="text-align:left; ">F</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">1</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">6</< font>td><tdstyle="text-align:right; ">14</< font>td><tdstyle="text-align:left; ">Son SURNAME</< font>td><tdstyle="text-align:left; ">M</< font>td><tdstyle="text-align:right; ">1</< font>td><tdstyle="text-align:right; ">2</< font>td><tdstyle="text-align:right; ">15</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">7</< font>td><tdstyle="text-align:right; ">14</< font>td><tdstyle="text-align:left; ">Son SURNAME</< font>td><tdstyle="text-align:left; ">M</< font>td><tdstyle="text-align:right; ">1</< font>td><tdstyle="text-align:right; ">2</< font>td><tdstyle="text-align:right; ">16</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">8</< font>td><tdstyle="text-align:right; ">15</< font>td><tdstyle="text-align:left; ">Jane GIRL</< font>td><tdstyle="text-align:left; ">F</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">14</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">9</< font>td><tdstyle="text-align:right; ">16</< font>td><tdstyle="text-align:left; ">Anne JONES</< font>td><tdstyle="text-align:left; ">F</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">14</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">10</< font>td><tdstyle="text-align:right; ">17</< font>td><tdstyle="text-align:left; ">Karen SURNAME</< font>td><tdstyle="text-align:left; ">F</< font>td><tdstyle="text-align:right; ">1</< font>td><tdstyle="text-align:right; ">2</< font>td><tdstyle="text-align:right; ">17</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">11</< font>td><tdstyle="text-align:right; ">18</< font>td><tdstyle="text-align:left; ">John DOE</< font>td><tdstyle="text-align:left; ">M</< font>td><td> </< font>td><td> </< font>td><tdstyle="text-align:right; ">16</< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">12</< font>td><tdstyle="text-align:right; ">52</< font>td><tdstyle="text-align:left; ">Jane SURNAME</< font>td><tdstyle="text-align:left; ">F</< font>td><tdstyle="text-align:right; ">14</< font>td><tdstyle="text-align:right; ">15</< font>td><td> </< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">13</< font>td><tdstyle="text-align:right; ">53</< font>td><tdstyle="text-align:left; ">James SURNAME</< font>td><tdstyle="text-align:left; ">M</< font>td><tdstyle="text-align:right; ">14</< font>td><tdstyle="text-align:right; ">15</< font>td><td> </< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">14</< font>td><tdstyle="text-align:right; ">54</< font>td><tdstyle="text-align:left; ">Julia SURNAME</< font>td><tdstyle="text-align:left; ">F</< font>td><tdstyle="text-align:right; ">14</< font>td><tdstyle="text-align:right; ">15</< font>td><td> </< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">15</< font>td><tdstyle="text-align:right; ">55</< font>td><tdstyle="text-align:left; ">Tom DOE</< font>td><tdstyle="text-align:left; ">M</< font>td><tdstyle="text-align:right; ">17</< font>td><tdstyle="text-align:right; ">16</< font>td><td> </< font>td></< font>tr><trstyle="height:18px ;"><tdstyle="font-size:8pt; background-color:#cacaca; text-align:center; ">16</< font>td><tdstyle="text-align:right; ">56</< font>td><tdstyle="text-align:left; ">Amy DOE</< font>td><tdstyle="text-align:left; ">F</< font>td><tdstyle="text-align:right; ">17</< font>td><tdstyle="text-align:right; ">16</< font>td><td> </< font>td></< font>tr></< font>table><br/><br/><spanstyle="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >></< font>span><astyle="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;"href="http://www.excel-jeanie-html.de/index.php?f=1"target="_blank"> Excel Jeanie HTML 4</</< font>a><o:p></o:p>
</< font></< font>
<o:p></o:p>
This was my sample code to get the first 3 columns how I wanted it. But doesn't get the multiple marriage instance.<o:p></o:p>
Code: 
Sub TEST()[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Dim x As Long, a As Long, b As Long, c As Long, d As Long[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Dim e As Long, f As Long[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'x = sets last used row for range[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'a = source Col, b = source Row[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'c = dest Row, d = dest Col[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'e = source Col, f = source Row (Source of Individual Id #)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Application.ScreenUpdating = False ' speed up code for macro[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Application.Calculation = xlCalculationManual ' speed up code for macro[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]c = 1<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]For a = 1 To 7 Step 3 'selects columns A D G[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]c = c 'begins Row 1[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]d = 1 'begins in Col A[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]x = Sheets("Testing").Cells(Rows.Count, a).End(xlUp).Row ' sets last row of source range[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]For b = 1 To x 'source range starts at row 1[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]If Sheets("Testing").Cells(b, a) <> "" Then ' excludes blanks in source range[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]If Sheets("Testing").Cells(b, a).Font.Bold Then 'if bold text[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]c = c + 1 'dest Row +1 (next row)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]d = 1 'Begins in dest Col A[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]e = a + 1[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]f = b[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Sheets("List").Cells(c, d) = Cells(f, e)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]d = d + 1 'dest next Col[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Sheets("List").Cells(c, d) = Cells(b, a)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'Copies Bold data from source to destination[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]d = d + 1 'dest next Col[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'================================[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'This section puts in Sex (M/F)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]If Sheets("Testing").Cells(b, a).Interior.ColorIndex > 2 Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Sheets("List").Cells(c, d).Value = "F"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Else[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Sheets("List").Cells(c, d).Value = "M"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'Copies Sex data from source to destination[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'================================[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'This section puts in parent Id #[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]'================================[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'This section puts in spouse Id #[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Else[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]'================================[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Next b 'goes to next Row in source sheet[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Next a 'goes to next Col in source sheet[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Application.Calculation = xlCalculationAutomatic ' set back to normal[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Application.ScreenUpdating = True ' set back to normal[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]MsgBox "Complete" 'Alert, macro finished[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]
<o:p></o:p>
Thanks again<o:p></o:p>
<o:p></o:p>
 
Last edited:
Upvote 0
I can see a whole bunch of xml ( html ) text instead of a sheet display ... what can everyone else see?
 
Last edited:
Upvote 0
GlennUK said:
I can see a whole bunch of xlm text instead of a sheet display ... what can everyone else see?
Click to expand...

I am guessing that is what I am seeing as well - it's a bunch of red, blue, and black text full of what looks like HTML tags.
 
Upvote 0
Data:

html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGH
1Male SURNAME1Son SURNAME14Jane SURNAME52
2b.b.b.
3d.m.
4bur.Jane GIRL15James SURNAME53
5m.b.b.
6Female MAIDEN2d.
7b.bur.Julia SURNAME54
8d.m.b.
9bur.Anne JONES16
10m.b.
11Female SMITH3
12b.Karen SURNAME17Tom DOE55
13d.b.b.
14bur.m.
15John DOE18Amy DOE56
16b.b.
Testing


</body></html>

Expected Results:

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEF
1IDNameSexFatherMotherSpouse
21Male SURNAMEM2
31Male SURNAMEM3
42Female MAIDENF1
53Female SMITHF1
614Son SURNAMEM1215
714Son SURNAMEM1216
815Jane GIRLF14
916Anne JONESF14
1017Karen SURNAMEF1217
1118John DOEM16
1252Jane SURNAMEF1415
1353James SURNAMEM1415
1454Julia SURNAMEF1415
1555Tom DOEM1716
1656Amy DOEF1716
List

</body></html>
 
Upvote 0
Thanks sous2817, much appreciated.

I have a question now ... how can you tell which mother the children in column D belong to?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Error message when executing macro
Replies
3
Views
313
rlv01
R
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
257
chazrab
C
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
597
mlan1604
M
C
  • Question Question
Search a Row starting at Specified Column
Replies
3
Views
86
Peter_SSs
Peter_SSs
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
435
maiwarits
M

Forum statistics

Threads
1,224,514
Messages
6,179,225
Members
452,896
Latest member
IGT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top