How to lineup 2 columns of data?
Posted by Brian Bankey on February 24, 2000 8:10 PM
I have 2 columns of sorted data (ex. customer numbers).
Not all of the numbers in the two columns
match each other. Some are unique in column A.
Some are unique in column B.
I would like to line up the cells in the 2 columns
on the same row where the numbers match.
Where there is no match I need to insert
a blank cell in the appropriate column (A or B).
The inserted cell corrects the alignment between
the 2 columns.
How would you do this?
Thanks,
Brian
Posted by Celia on February 25, 2000 3:45 AM
:
Brian
This can, of course, be done by a macro but here is a way without creating a macro :-
Lets say your figures are in Column A (rows 1 to 10) and in Column B.
(1)Enter the following array formula in cell C1 and drag in down to the last row of Column B.
=IF(COUNTIF($A1:$A10,B1)=0,B1,)
This puts into Column C the figures in Column B which do not appear in Column A
(2)Select Column C and then select Edit/Go To
/Special
/Formulas-Numbers (make sure the other boxes under formulas are un-checked)
This should highlight the figures in Column C.
(3)Select Edit/Copy and then put the figures at the bottom of the Column A list by Paste Special/Values.
(4)Select Column A and sort into sequence.
(5)Column A should now be the merged figures.
Celia
