Data

[sohaibj]

I've data like this in thousand of lines:

Emp Code Name Relationship
722 A EMPLOYEE
722 B SPOUSE
722 G SPOUSE
722 H DAUGHTER
722 P SON
722 W SON

Now I want to insert following in last column "Output"

Emp Code Name Relationship Ouput
722 A EMPLOYEE EMPLOYEE
722 B SPOUSE SPOUSE1
722 G SPOUSE SPOUSE2
722 H DAUGHTER CHILD1
722 P SON CHILD2
722 W SON CHILD3

Maximum Spouse number could be 4
Maximum Chil number could be 12

 

[Peter_SSs]

This assumes data starts in cell A1 and should cope with any number of spouses/children.

Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Add_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> rData <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Data, Results<br>    <SPAN style="color:#00007F">Dim</SPAN> L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, C <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rData = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)<br>    <SPAN style="color:#00007F">With</SPAN> rData<br>        Data = .Value<br>        .Columns(3).Replace _<br>            What:="DAUGHTER", Replacement:="CHILD", MatchCase:=<SPAN style="color:#00007F">False</SPAN><br>        .Columns(3).Replace _<br>            What:="SON", Replacement:="CHILD", MatchCase:=<SPAN style="color:#00007F">False</SPAN><br>        Results = .Resize(, 4).Value<br>        Results(1, 4) = "Output"<br>        L = <SPAN style="color:#00007F">UBound</SPAN>(Data, 1)<br>        <SPAN style="color:#00007F">For</SPAN> R = 2 <SPAN style="color:#00007F">To</SPAN> L<br>            <SPAN style="color:#00007F">If</SPAN> Results(R, 1) = Results(R - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> Results(R, 3) <> Results(R - 1, 3) <SPAN style="color:#00007F">Then</SPAN><br>                    C = 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                Results(R, 4) = Results(R, 3) & " " & C<br>                C = C + 1<br>            <SPAN style="color:#00007F">Else</SPAN><br>                Results(R, 4) = Results(R, 3)<br>                C = 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> R<br>        .Resize(, 4).Value = Results<br>        .Value = Data<br>        .Offset(, .Columns.Count).Resize(, 1).EntireColumn.AutoFit<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br></FONT>

 

[Richard Schollar]

Here's a formula way that should work:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style=";">Emp</td><td width="63px" style=";">Code Name</td><td width="63px" style=";">Relationship</td><td width="63px" style=";">Output</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style="text-align: right;;">722</td><td width="63px" style=";">A</td><td width="63px" style=";">EMPLOYEE</td><td width="63px" style=";">EMPLOYEE</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style="text-align: right;;">722</td><td width="63px" style=";">B</td><td width="63px" style=";">SPOUSE</td><td width="63px" style=";">SPOUSE1</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style="text-align: right;;">722</td><td width="63px" style=";">G</td><td width="63px" style=";">SPOUSE</td><td width="63px" style=";">SPOUSE2</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style="text-align: right;;">722</td><td width="63px" style=";">H</td><td width="63px" style=";">DAUGHTER</td><td width="63px" style=";">CHILD1</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style="text-align: right;;">722</td><td width="63px" style=";">P</td><td width="63px" style=";">SON</td><td width="63px" style=";">CHILD2</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style="text-align: right;;">722</td><td width="63px" style=";">W</td><td width="63px" style=";">SON</td><td width="63px" style=";">CHILD3</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">8</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">P</td><td width="63px" style=";">EMPLOYEE</td><td width="63px" style=";">EMPLOYEE</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">9</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">A</td><td width="63px" style=";">SPOUSE</td><td width="63px" style=";">SPOUSE1</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">10</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">D</td><td width="63px" style=";">SON</td><td width="63px" style=";">CHILD1</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">11</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">E</td><td width="63px" style=";">SPOUSE</td><td width="63px" style=";">SPOUSE2</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">12</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">R</td><td width="63px" style=";">DAUGHTER</td><td width="63px" style=";">CHILD2</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">13</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">F</td><td width="63px" style=";">DAUGHTER</td><td width="63px" style=";">CHILD3</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">14</td><td width="63px" style="text-align: right;;">825</td><td width="63px" style=";">B</td><td width="63px" style=";">DAUGHTER</td><td width="63px" style=";">CHILD4</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">15</td><td width="63px" style="text-align: right;;">965</td><td width="63px" style=";">T</td><td width="63px" style=";">DAUGHTER</td><td width="63px" style=";">CHILD1</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">16</td><td width="63px" style="text-align: right;;">965</td><td width="63px" style=";">L</td><td width="63px" style=";">EMPLOYEE</td><td width="63px" style=";">EMPLOYEE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">{=IF(<font color="Blue">OR(<font color="Red">$C2={"SON","DAUGHTER"}</font>),"CHILD",$C2</font>)&IF(<font color="Blue">C2="EMPLOYEE","",SUM(<font color="Red">IF(<font color="Green">$A$2:$A2=A2,IF(<font color="Purple">$C2="SPOUSE",(<font color="Teal">$C$2:$C2=C2</font>)+0,(<font color="Teal">$C$2:$C2="SON"</font>)+(<font color="Teal">$C$2:$C2="DAUGHTER"</font>)</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Peter_SSs]

Based partly on Richard's formula, possibly this non-array formula also. Unlike Richard's formula, mine does rely on any spouses and children for an 'Emp Code' being grouped together as in your original sample.

Excel Workbook
	A	B	C	D
1	Emp Code	Name	Relationship	Output
2	722		EMPLOYEE	EMPLOYEE
3	722		SPOUSE	SPOUSE1
4	722		SPOUSE	SPOUSE2
5	722		DAUGHTER	CHILD1
6	722		SON	CHILD2
7	722		SON	CHILD3
8	800		EMPLOYEE	EMPLOYEE
9	723		EMPLOYEE	EMPLOYEE
10	723		SPOUSE	SPOUSE1
11	723		DAUGHTER	CHILD1
12	999		EMPLOYEE	EMPLOYEE
13	999		SON	CHILD1
14	999		SON	CHILD2
15	999		SON	CHILD3
16	999		DAUGHTER	CHILD4
17	999		DAUGHTER	CHILD5
18	999		SON	CHILD6
19	999		SON	CHILD7
20	999		DAUGHTER	CHILD8
21	999		SON	CHILD9
22	999		SON	CHILD10
23	999		DAUGHTER	CHILD11
24	999		SON	CHILD12
Employees (3)