Another way:
G1 is =match(A1,B:B,0)
H1 is =indirect("B"&G1)
I1 is =indirect("C"&G1)
J1 is =indirect("D"&G1)
etc
Copy down
-----Original Message-----
"Don S" wrote:
On Wed, 23 Feb 2005 08:37:12 -0800, "Mcobra41"
wrote:
Ok, I have a list of numbers in one column (14,000+)
and I have a list in a
second column (12,000+) The numbers in the second
column are equal to a
number in the first column. I need to have a side by
side comparison of the
two lists. With a blank cell representing any number
not on the second
column that is on the first column. There are 2000
numbers missing in the
second column and I would like to two columns to show
side by side.
Here is a basic example of what i am trying to do,
2145154 2145154
2140020 2140020
2098960 2098960
2115448
2115450 2115450
2140019 2140019
2149646
2147722 2147722
Basically I have been going in hand by hand and
inserting a blank cell to
push the cells down. Which I have to go through every
number individually
and with 14,000 numbers it has become quite a pain. I
am wondering if their
is some sort of filter or sort function that could
place the numbers in the
right column to be equal to their number in the left
column?
This is quick and ugly.
You have columns A & B. Sort or column B and enter the
following:
Column C "=A1", Column D = "=VLOOKUP
(C1,$B$1:$B$12000,1,FALSE)"
Copy the formulas in C & D to the bottom of your data.
Replace the #N/A in Column D with Blanks
Convert Columns C & D to Values (copy/paste value)
Resort on column A.
This will get it done, but I'm sure there are cleaner
methods.
Don S
Great Don, thanks for the help. Here is another wrench
to add in this
problem of mine, if you don't mind helping me witht his
one as well.
2145154 2145154 Text Text Text Text
2140020 2140020 Text Text Text Text
2098960 2098960 Text Text Text Text
2115448
2115450 2115450 Text Text Text Text
2140019 2140019 Text Text Text Text
2149646
2147722 2147722 Text Text Text Text
With each number in the second column I have 4 other
columns of text that
correspond with that specific number in column 2. I need
to have all 5
columns move at the same time, can this be done with your
formula or added
into your formula? This will save me about 40-50 hours
of just basically
busy work that needs to get done. I really appreciate
your help.
.
|