Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Format numbers from percentage points to basis points | Excel Discussion (Misc queries) | |||
Returning a value in excel my matching 2 data points | Excel Discussion (Misc queries) | |||
Verifying and moving non matching numbers in columns | Excel Worksheet Functions | |||
How to create reports based on data residing in multiple workbooks | Excel Discussion (Misc queries) | |||
Matching data in columns | Excel Discussion (Misc queries) |