Alligning two collums
Posted by Sander Visser on August 18, 2001 6:02 AM
Hi,
This one already bothers my for years.
Suppose you have two collums of data. The first twelve rows in the first collum containts the names of all 12 months of the year perfectly sorted from january to december. The first four rows of the second collum contains the names of 4 random months.
Now I would lake to have the months in the second collum on the same row as in the first collum (so that I would have 8 empty rows in the second collum).
The end reslut would be for example
Columm 1:
January
February
March
April
etc...
Collum 2:
(empty)
February
(empty)
(empty)
etc...
Is there any quick way to do this?????
Posted by Mark W. on August 18, 2001 12:23 PM
This can be accomplished using a 3rd column. Suppose
cells A1:A12 contains...
{"January";"February";"March";"April";"May";"June"
;"July";"August";"September";"October";"November"
;"December"}
...and B1:B12 contains...
{"";"";"";"December";"February";"";"";"March";"";"July";"";""}
You could enter the array formula, {=IF(OR(A1=$B$1:$B$12),A1,"")},
into cell C1 and Copy down to cell C12. Afterwards
you can Copy the values in column C and Paste/Special
Values onto column B.
Note: Array formulas are entered using the
Control+Shift+Enter key combination. The braces,
{}, are not typed by you -- they're supplied by
Excel in recognition of a properly entered array
formula.
