matching like numbers/data points residing in different columns

Hello,

I apologize if this question has already been answered, but I was
unable to find an answer by searching the message archives. Perhaps
this is because I am not exactly sure how to describe the function that
I want to perform.

I have to columns of numbers/data points. Column A contains say 35,000
numbers/data points and Column B contains only 33,000 data points. For
every number/data point in column B there is an identical number/data
points in column A. Basically, 2000 data points have been removed from
coumn B. I want to align (or perhaps sort) the columns such that
identical data points are aligned by row. When there is no
corresponding number in column B for the number in column A a blank
cell will be in column B -- indicating the lack of an identical number.
If this was not clear (which I'm sure it wasn't), below is a much
simpler version of what my data looks like now and what I would like it
to like.

What the data looks like now:

Col A Col B
9 9
8 8
7 6
6 5
5 4
4 2
3 1
2
1

What I would like the data to look like:
Col A Col B
9 9
8 8
7
6 6
5 5
4 4
3
2 2
1 1


Thank you,

Brad White
Graduate Student
Center for Global Health and Infectious Diseases
Department of Biological Sciences
University of Notre Dame

5

So you're saying: you want blanks in col B if they don't appear in col A?

In B1, put the formula =VLOOKUP($B1,$A:$A,1,0)

then fill down in col B to the bottom and replace all #N?A in col B with blanks


Col

The last response won't work because you'll only match the values in you'll get a circular reference. In cell B1, you cannot put the formula =VLOOKUP($B1,$A:A4,1,0).

Try this macro:

Sub SORT_TWO_COLUMNS()

'sort columnA and sort columnB
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'sort the 2 columns
Range("A1").Select
Do While ActiveCell.Value <> "" Or ActiveCell.Offset(0, 1).Value <> ""
LEFT_VALUE = ActiveCell.Value
RIGHT_VALUE = ActiveCell.Offset(0, 1).Value
If LEFT_VALUE < RIGHT_VALUE Then
Selection.Insert Shift:=xlDown 'shift columA down 1 row
ActiveCell.Offset(1, 0).Select 'move down 1 row
ElseIf LEFT_VALUE > RIGHT_VALUE Then
ActiveCell.Offset(0, 1).Select 'move to columnB
Selection.Insert Shift:=xlDown 'shift columB down 1 row
ActiveCell.Offset(1, -1).Select 'move back to columnA and down 1 row
Else
ActiveCell.Offset(1, 0).Select 'no differences....move down 1 row
End If
Loop
End Sub


This macro assumes that your data set start on Row1. If you require, I can forward you an example spreadsheet with the macro. My email is [email protected]