Merging columns

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>