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

matching like numbers/data points residing in different columns
 

So you're saying: you want blanks in col B if they don't appear in co
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
with blanks


Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=57197

matching like numbers/data points residing in different columns
 

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



--
citrix789
------------------------------------------------------------------------
citrix789's Profile: http://www.excelforum.com/member.php...o&userid=37568
View this thread: http://www.excelforum.com/showthread...hreadid=571977