Hi all,

I have got 3 columns with the column headings as follows

Column : A B C


Firstname Fathersname Mothersname

Jim Jack Mary
Raj
Rani
Susan John Diana

The desired output is given below

Column D E
Jim Jack
Jim Mary
Raj Rani
Susan John
Susan Diana

The logic should be in case if a person has father & mother then he
should have 2 rows in the output, if not he would have only one row in
the resulting set.

Do let me know how do i do this, i have got 500 such records to
update.

3 Cheers
Arun


--
daarun
------------------------------------------------------------------------
daarun's Profile: http://www.excelforum.com/member.php...o&userid=28954
View this thread: http://www.excelforum.com/showthread...hreadid=486827

Assuming that your data starts with the headers in row 1 and are in columns A to C:

In cell D2, use the formula
=A2
In cell E2, use the formula
=B2
In cell F2, use the formula
=ROW()
In cell G2, use the formula
=IF(C2<"",A2,"")
In cell H2, use the formula
=C2
In cell I2, use the formula
=IF*C2<"",ROW(),"")

Then copy those six formulas down to match your 500 or so rows.

Then copy columns D:I, paste special values. Then select the used cells in columns G, H and I, cut
them, and paste them below the data in columns D, E and F. Then select Columns D, E and F, and sort
based on column D, then reselect to get rid of the blanks, and resort on column F.

HTH,
Bernie
MS Excel MVP


"daarun" wrote in message
...

Hi all,

I have got 3 columns with the column headings as follows

Column : A B C


Firstname Fathersname Mothersname

Jim Jack Mary
Raj
Rani
Susan John Diana

The desired output is given below

Column D E
Jim Jack
Jim Mary
Raj Rani
Susan John
Susan Diana

The logic should be in case if a person has father & mother then he
should have 2 rows in the output, if not he would have only one row in
the resulting set.

Do let me know how do i do this, i have got 500 such records to
update.

3 Cheers
Arun


--
daarun
------------------------------------------------------------------------
daarun's Profile: http://www.excelforum.com/member.php...o&userid=28954
View this thread: http://www.excelforum.com/showthread...hreadid=486827