Merging columns

[daarun]

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

 

[Bernie Deitrick]

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