Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mcobra41
 
Posts: n/a
Default Sort or Filter option?

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?
  #2   Report Post  
Don S
 
Posts: n/a
Default

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
  #3   Report Post  
Mcobra41
 
Posts: n/a
Default



"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.
  #4   Report Post  
BobT
 
Posts: n/a
Default

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.
.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel subtotals, add a sort option, and BOLD the function answers crescent97526 New Users to Excel 4 December 21st 05 05:30 PM
How do I filter or sort by cell shading? urnojfk Excel Discussion (Misc queries) 2 January 11th 05 05:05 PM
Excel Auto Filter: WHY'S SORT @ TOP OF LIST? WHEN I KEY TO "SHOW . Dan W Excel Worksheet Functions 0 December 1st 04 04:53 PM
The Auto Filter button lost the column specified option. D Excel Worksheet Functions 1 November 5th 04 12:47 AM
sort option is greyed out in excel mom Excel Worksheet Functions 1 November 3rd 04 04:22 PM


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"