A Macro to find missing serial numbers in a column

In a column, serial numbers are typed as a reference. For example from 1 to
173. How ever actual number of?raws is 175 (obvious by checking raw numbers).
2 numbers are missing. Let say for example 17 and 56 are not typed.
What is the easiest way to find these raws?
Is there any written MAcro, to find these raws?
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

A Macro to find missing serial numbers in a column

One option using non-array formulas ..

Assume source numbers are listed in A2 down
(A1 assumed empty or containing a text col header)

Using 2 empty cols to the right, eg cols E and F

Put in E2:
=IF(A2="","",IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW()))
Copy E2 down by 175* rows to E176
*this number is assumed known

Then place in F2:
=IF(ROW(A1)>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW(A1)))-1)
Copy F2 down until "blanks" appear
The missing serial numbers will be extracted at the top in col F
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khoshravan" wrote:
> In a column, serial numbers are typed as a reference. For example from 1 to
> 173. How ever actual number of　raws is 175 (obvious by checking raw numbers).
> 2 numbers are missing. Let say for example 17 and 56 are not typed.
> What is the easiest way to find these raws?
> Is there any written MAcro, to find these raws?
> --
> Rasoul Khoshravan Azar
> Kobe University, Kobe, Japan
>
> A Macro to find missing serial numbers in a column

Dear Max
Thanks for your nice and talented solution.
It gives 2 missing numbers so I understand that my colleague has typed 116
three times for 114 and 115. Also 2 other number numbers are reported which
is not in my A:A column. Last two numbers, 175, 176. Anyway that is minor
issue beyond my need.
But more interestingly I would like to understand the logic beyond your
solution. Although your solution is two simple combination of commands but it
seems very complicated for me and beyond my knowledge to understand. If
possible please give some hints what these commands do. Thanks a lot.
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Max" wrote:

> One option using non-array formulas ..
>
> Assume source numbers are listed in A2 down
> (A1 assumed empty or containing a text col header)
>
> Using 2 empty cols to the right, eg cols E and F
>
> Put in E2:
> =IF(A2="","",IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW()))
> Copy E2 down by 175* rows to E176
> *this number is assumed known
>
> Then place in F2:
> =IF(ROW(A1)>COUNT(E:E),"",INDEX(E:E,SMALL(E:E,ROW(A1)))-1)
> Copy F2 down until "blanks" appear
> The missing serial numbers will be extracted at the top in col F
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Khoshravan" wrote:
> > In a column, serial numbers are typed as a reference. For example from 1 to
> > 173. How ever actual number of　raws is 175 (obvious by checking raw numbers).
> > 2 numbers are missing. Let say for example 17 and 56 are not typed.
> > What is the easiest way to find these raws?
> > Is there any written MAcro, to find these raws?
> > --
> > Rasoul Khoshravan Azar
> > Kobe University, Kobe, Japan
> >
> > A Macro to find missing serial numbers in a column

You're welcome, Rasoul!

Some key clarifications, as requested:

ROW(A1) is used as an "internal" incrementer within the formulas in cols E
and F. It simply generates the series: 1,2,3 .... as we copy the formula
down. To see this happening, just put in any starting cell, say in K2:
=ROW(A1), then copy K2 down.

In the criteria col E, the core MATCH(ROW(A1),A:A,0) is simply a way for us
to "internally" generate the complete series:1,2,3,... 175 as the lookup
values to exact-match with what's in col A as we copy down. ISNUMBER is then
wrapped around the MATCH's returns (which are either numbers or #N/As,
depending on whether there's a match found) to yield a TRUE or FALSE for use
within the IF construct. Essentially when we copy E2 down, we are just
simultaneously checking & flagging out missing serial numbers in col A (where
MATCH = #N/A) via arb row numbers.

The core part in col F is essentially just the SMALL part viz.
we could have used in F2: =SMALL(E:E,ROW(A1))-1
then copy down until #NUM! appears to extract the desired results
As in col E, ROW(A1) is used as the internal incrementer here for SMALL to
extract the smallest value from col E, then the 2nd smallest, 3rd smallest,
etc as we copy down, The "-1" part is just an arithmetic adjustment to the
values returned by SMALL to produce the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khoshravan" wrote:
> Dear Max
> Thanks for your nice and talented solution.
> It gives 2 missing numbers so I understand that my colleague has typed 116
> three times for 114 and 115. Also 2 other number numbers are reported which
> is not in my A:A column. Last two numbers, 175, 176. Anyway that is minor
> issue beyond my need.
> But more interestingly I would like to understand the logic beyond your
> solution. Although your solution is two simple combination of commands but it
> seems very complicated for me and beyond my knowledge to understand. If
> possible please give some hints what these commands do. Thanks a lot.
> --
> Rasoul Khoshravan Azar
> Kobe University, Kobe, Japan

Dear Max
Thanks for your explanation. I am trying to digest your solution.
Before entering details, I have a simple question.
I used Ctrl+arrow key in F col to reach the row which is reported as
missing, but it doesn't stop at row 116 and directly goes to end of column
but there is a number (116) in that row and ctrl+arrow just ignores it. I
can' explain to myself why this happens.
I really appreciate your talent and Excel knowledge regarding nice solution
just by combining simple commands to generate powerfull ones.
I will write later after completeing a practice of your commands and also I
will give a comment about your site which just viewed.
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Max" wrote:

> You're welcome, Rasoul!
>
> Some key clarifications, as requested:
>
> ROW(A1) is used as an "internal" incrementer within the formulas in cols E
> and F. It simply generates the series: 1,2,3 .... as we copy the formula
> down. To see this happening, just put in any starting cell, say in K2:
> =ROW(A1), then copy K2 down.
>
> In the criteria col E, the core MATCH(ROW(A1),A:A,0) is simply a way for us
> to "internally" generate the complete series:1,2,3,... 175 as the lookup
> values to exact-match with what's in col A as we copy down. ISNUMBER is then
> wrapped around the MATCH's returns (which are either numbers or #N/As,
> depending on whether there's a match found) to yield a TRUE or FALSE for use
> within the IF construct. Essentially when we copy E2 down, we are just
> simultaneously checking & flagging out missing serial numbers in col A (where
> MATCH = #N/A) via arb row numbers.
>
> The core part in col F is essentially just the SMALL part viz.
> we could have used in F2: =SMALL(E:E,ROW(A1))-1
> then copy down until #NUM! appears to extract the desired results
> As in col E, ROW(A1) is used as the internal incrementer here for SMALL to
> extract the smallest value from col E, then the 2nd smallest, 3rd smallest,
> etc as we copy down, The "-1" part is just an arithmetic adjustment to the
> values returned by SMALL to produce the required results.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Khoshravan" wrote:
> > Dear Max
> > Thanks for your nice and talented solution.
> > It gives 2 missing numbers so I understand that my colleague has typed 116
> > three times for 114 and 115. Also 2 other number numbers are reported which
> > is not in my A:A column. Last two numbers, 175, 176. Anyway that is minor
> > issue beyond my need.
> > But more interestingly I would like to understand the logic beyond your
> > solution. Although your solution is two simple combination of commands but it
> > seems very complicated for me and beyond my knowledge to understand. If
> > possible please give some hints what these commands do. Thanks a lot.
> > --
> > Rasoul Khoshravan Azar
> > Kobe University, Kobe, Japan

Dear Max
I viewed your site.
It seams that you have put vast amount of valuable information in your site.
However there is no indexing or any reasonable way to find what the viewer
are looking for?
Do you have any comment on how to search your site for the info I am looking
for?
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Max" wrote:

> You're welcome, Rasoul!
>
> Some key clarifications, as requested:
>
> ROW(A1) is used as an "internal" incrementer within the formulas in cols E
> and F. It simply generates the series: 1,2,3 .... as we copy the formula
> down. To see this happening, just put in any starting cell, say in K2:
> =ROW(A1), then copy K2 down.
>
> In the criteria col E, the core MATCH(ROW(A1),A:A,0) is simply a way for us
> to "internally" generate the complete series:1,2,3,... 175 as the lookup
> values to exact-match with what's in col A as we copy down. ISNUMBER is then
> wrapped around the MATCH's returns (which are either numbers or #N/As,
> depending on whether there's a match found) to yield a TRUE or FALSE for use
> within the IF construct. Essentially when we copy E2 down, we are just
> simultaneously checking & flagging out missing serial numbers in col A (where
> MATCH = #N/A) via arb row numbers.
>
> The core part in col F is essentially just the SMALL part viz.
> we could have used in F2: =SMALL(E:E,ROW(A1))-1
> then copy down until #NUM! appears to extract the desired results
> As in col E, ROW(A1) is used as the internal incrementer here for SMALL to
> extract the smallest value from col E, then the 2nd smallest, 3rd smallest,
> etc as we copy down, The "-1" part is just an arithmetic adjustment to the
> values returned by SMALL to produce the required results.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Khoshravan" wrote:
> > Dear Max
> > Thanks for your nice and talented solution.
> > It gives 2 missing numbers so I understand that my colleague has typed 116
> > three times for 114 and 115. Also 2 other number numbers are reported which
> > is not in my A:A column. Last two numbers, 175, 176. Anyway that is minor
> > issue beyond my need.
> > But more interestingly I would like to understand the logic beyond your
> > solution. Although your solution is two simple combination of commands but it
> > seems very complicated for me and beyond my knowledge to understand. If
> > possible please give some hints what these commands do. Thanks a lot.
> > --
> > Rasoul Khoshravan Azar
> > Kobe University, Kobe, Japan

Dear Max
I studied your commands carefully.
I have following question:
Command IF(ISNUMBER(MATCH(ROW(A1);A:A;0));"";ROW())
and IF(A3="";"";IF(ISNUMBER(MATCH(ROW(A2);A:A;0));"";ROW()))
basically return same result. So I couldn't understand the reason for first
IF (IF(A3=""...)
The structure of this first IF is not clear for me. Is it possible to
explain its functionality? Thanks
--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan


"Khoshravan" wrote:

> Dear Max
> I viewed your site.
> It seams that you have put vast amount of valuable information in your site.
> However there is no indexing or any reasonable way to find what the viewer
> are looking for?
> Do you have any comment on how to search your site for the info I am looking
> for?
> --
> Rasoul Khoshravan Azar
> Kobe University, Kobe, Japan
>
>
> "Max" wrote:
>
> > You're welcome, Rasoul!
> >
> > Some key clarifications, as requested:
> >
> > ROW(A1) is used as an "internal" incrementer within the formulas in cols E
> > and F. It simply generates the series: 1,2,3 .... as we copy the formula
> > down. To see this happening, just put in any starting cell, say in K2:
> > =ROW(A1), then copy K2 down.
> >
> > In the criteria col E, the core MATCH(ROW(A1),A:A,0) is simply a way for us
> > to "internally" generate the complete series:1,2,3,... 175 as the lookup
> > values to exact-match with what's in col A as we copy down. ISNUMBER is then
> > wrapped around the MATCH's returns (which are either numbers or #N/As,
> > depending on whether there's a match found) to yield a TRUE or FALSE for use
> > within the IF construct. Essentially when we copy E2 down, we are just
> > simultaneously checking & flagging out missing serial numbers in col A (where
> > MATCH = #N/A) via arb row numbers.
> >
> > The core part in col F is essentially just the SMALL part viz.
> > we could have used in F2: =SMALL(E:E,ROW(A1))-1
> > then copy down until #NUM! appears to extract the desired results
> > As in col E, ROW(A1) is used as the internal incrementer here for SMALL to
> > extract the smallest value from col E, then the 2nd smallest, 3rd smallest,
> > etc as we copy down, The "-1" part is just an arithmetic adjustment to the
> > values returned by SMALL to produce the required results.
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > xdemechanik
> > ---
> > "Khoshravan" wrote:
> > > Dear Max
> > > Thanks for your nice and talented solution.
> > > It gives 2 missing numbers so I understand that my colleague has typed 116
> > > three times for 114 and 115. Also 2 other number numbers are reported which
> > > is not in my A:A column. Last two numbers, 175, 176. Anyway that is minor
> > > issue beyond my need.
> > > But more interestingly I would like to understand the logic beyond your
> > > solution. Although your solution is two simple combination of commands but it
> > > seems very complicated for me and beyond my knowledge to understand. If
> > > possible please give some hints what these commands do. Thanks a lot.
> > > --
> > > Rasoul Khoshravan Azar
> > > Kobe University, Kobe, Japan

Thanks for raising this.

Just realized that the earlier
> Put in E2:
> =IF(A2="","",IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW()))
should have been *without* the front check for empty cells in col A, viz.
teh part: IF(A2="","",...) as this would have returned wrong results if
indeed there were empty cells in col A

It should just be, like you mentioned, in E2, copied down by 175 rows:
=IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),"",ROW())
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khoshravan" wrote:
> Dear Max
> I studied your commands carefully.
> I have following question:
> Command IF(ISNUMBER(MATCH(ROW(A1);A:A;0));"";ROW())
> and IF(A3="";"";IF(ISNUMBER(MATCH(ROW(A2);A:A;0));"";ROW()))
> basically return same result. So I couldn't understand the reason for first
> IF (IF(A3=""...)
> The structure of this first IF is not clear for me. Is it possible to
> explain its functionality? Thanks
> --
> Rasoul Khoshravan Azar
> Kobe University, Kobe, Japan

> I used Ctrl+arrow key in F col to reach the row which is reported as
> missing, but it doesn't stop at row 116 and directly goes to end of column
> but there is a number (116) in that row and ctrl+arrow just ignores it. I
> can' explain to myself why this happens.

CTRL+Down within col F's filled formula range will simply bring you right
down to the end of the formula filled range. That's how it works. You have to
scroll down manually.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Khoshravan" wrote:
> ... you have put vast amount of valuable information in your site.
> However there is no indexing or any reasonable way
> to find what the viewer are looking for?
> Do you have any comment on how to search your site
> for the info I am looking for?

First, thanks for your comments. It's really just a free download archive
for the excel sample files which complement some of my responses in the excel
newsgroups. I've tried to front as much info as possible in the download
lists' filenames/file descripts itself, working within the max # of
characters allowed for by the free filehost (savefile.com) for filenames/file
descripts. But rest assured that full details are provided inside the files
-- as mentioned in my archives' welcoming message <g>.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---