Sort or Filter option?

[Mcobra41]

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?

 

[Don S]

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

 

[Mcobra41]

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.

 

[BobT]

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