Sort multiple columns to look for gaps
 

Excel 2003 - Is there anyway to sort multiple columns to look for gaps. What
I have is 8 different spreadsheets and he spreadsheet contains about 64,000
lines (almost the max Excel allows). There is a column in each spreadsheet
that is a unique identifier and should not contain a gap. However, the
numbers are in no particular order. So for instance, spreadsheet 1 contains
the numbers 1,2,4. Spreadsheet number 3 might contain the number 3 so there
is no gap.

Due to the large nature of the spreadsheet, I cannot put all these numbers
into on column in one spreadsheet and just sort for the answer. Is there a
way to put a bunch of different columns into one spreadsheet and then do a
sort of somekind to look for any gaps between the numbers?

Thanks!

Sort multiple columns to look for gaps
 

You can do something like this in each table - (Column A has your
sequential numbers. This formula would be in column B). Sort Column
A first:

=IF(A3<A2+1,"Error","OK")

In this example, create a new column (B) next to your sequential
column (A). Assuming the numbers are formatted as numbers, and they
are sequential in increments of 1, this will tell you where the gaps
are.

Copy this formula down the column. Then sort by column B and you will
immediately see the gaps next to the word "Error"

Sort multiple columns to look for gaps
 

Select the top cell of the suspect column.
While holding down the Ctrl button, tap the down arrow key.
Repeat as necessary.

-or-

Select the suspect column.
Go to Edit (menu) | Go To | Special (button) | Blanks
Click Ok.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"duketter"
wrote in message
Excel 2003 - Is there anyway to sort multiple columns to look for gaps. What
I have is 8 different spreadsheets and he spreadsheet contains about 64,000
lines (almost the max Excel allows). There is a column in each spreadsheet
that is a unique identifier and should not contain a gap. However, the
numbers are in no particular order. So for instance, spreadsheet 1 contains
the numbers 1,2,4. Spreadsheet number 3 might contain the number 3 so there
is no gap.

Due to the large nature of the spreadsheet, I cannot put all these numbers
into on column in one spreadsheet and just sort for the answer. Is there a
way to put a bunch of different columns into one spreadsheet and then do a
sort of somekind to look for any gaps between the numbers?
Thanks!

Sort multiple columns to look for gaps
 

I am not sure either of these will work. I have sequential numbers possible
in different columns. Column one might include the numbers 1,2,5. Column
two might include the number 3 and column three might include the number 4.
So there is not a gap. I need to review all the columns to see if there are
gaps. Not just one column? Maybe I am reading your responses wrong?

"Jim Cone" wrote:


Select the top cell of the suspect column.
While holding down the Ctrl button, tap the down arrow key.
Repeat as necessary.

-or-

Select the suspect column.
Go to Edit (menu) | Go To | Special (button) | Blanks
Click Ok.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"duketter"
wrote in message
Excel 2003 - Is there anyway to sort multiple columns to look for gaps. What
I have is 8 different spreadsheets and he spreadsheet contains about 64,000
lines (almost the max Excel allows). There is a column in each spreadsheet
that is a unique identifier and should not contain a gap. However, the
numbers are in no particular order. So for instance, spreadsheet 1 contains
the numbers 1,2,4. Spreadsheet number 3 might contain the number 3 so there
is no gap.

Due to the large nature of the spreadsheet, I cannot put all these numbers
into on column in one spreadsheet and just sort for the answer. Is there a
way to put a bunch of different columns into one spreadsheet and then do a
sort of somekind to look for any gaps between the numbers?
Thanks!

Sort multiple columns to look for gaps
 

What I posted will not help.
I read your post as if "gap" meant blank.
Jim Cone


"duketter"
wrote in message
I am not sure either of these will work. I have sequential numbers possible
in different columns. Column one might include the numbers 1,2,5. Column
two might include the number 3 and column three might include the number 4.
So there is not a gap. I need to review all the columns to see if there are
gaps. Not just one column? Maybe I am reading your responses wrong?

Sort multiple columns to look for gaps
 

One thing is to use the Excel ROW numbers to companr against your columns, or
make some real sequential columns as you would like them to be and compare
against your existing columns..........

Vaya con Dios,
Chuck, CABGx3



"duketter" wrote:

Excel 2003 - Is there anyway to sort multiple columns to look for gaps. What
I have is 8 different spreadsheets and he spreadsheet contains about 64,000
lines (almost the max Excel allows). There is a column in each spreadsheet
that is a unique identifier and should not contain a gap. However, the
numbers are in no particular order. So for instance, spreadsheet 1 contains
the numbers 1,2,4. Spreadsheet number 3 might contain the number 3 so there
is no gap.

Due to the large nature of the spreadsheet, I cannot put all these numbers
into on column in one spreadsheet and just sort for the answer. Is there a
way to put a bunch of different columns into one spreadsheet and then do a
sort of somekind to look for any gaps between the numbers?

Thanks!

Sort multiple columns to look for gaps
 

Can you create a helper column? Depending on how you identify what column
the number is in, you can use something like:

=if(a1<"",a1,if(a2<"",a2,a3))

Once all the numbers are in one column, the other suggestions will work.

Regards,
Fred

"duketter" wrote in message
...
I am not sure either of these will work. I have sequential numbers
possible
in different columns. Column one might include the numbers 1,2,5. Column
two might include the number 3 and column three might include the number
4.
So there is not a gap. I need to review all the columns to see if there
are
gaps. Not just one column? Maybe I am reading your responses wrong?

"Jim Cone" wrote:


Select the top cell of the suspect column.
While holding down the Ctrl button, tap the down arrow key.
Repeat as necessary.

-or-

Select the suspect column.
Go to Edit (menu) | Go To | Special (button) | Blanks
Click Ok.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"duketter"
wrote in message
Excel 2003 - Is there anyway to sort multiple columns to look for gaps.
What
I have is 8 different spreadsheets and he spreadsheet contains about
64,000
lines (almost the max Excel allows). There is a column in each
spreadsheet
that is a unique identifier and should not contain a gap. However, the
numbers are in no particular order. So for instance, spreadsheet 1
contains
the numbers 1,2,4. Spreadsheet number 3 might contain the number 3 so
there
is no gap.

Due to the large nature of the spreadsheet, I cannot put all these
numbers
into on column in one spreadsheet and just sort for the answer. Is there
a
way to put a bunch of different columns into one spreadsheet and then do
a
sort of somekind to look for any gaps between the numbers?
Thanks!