I have an Excel spreadsheet that contains two worksheets. The first contains
a set of data records - one row per record.

In the second worksheet, I would like to create a column which lists, in
numerical order, the row numbers of the first worksheet where the data in
column C is equal to the word 'HOME'.

I've thought of a way of doing this but it involves adding an extra hidden
column to the first worksheet which I would prefer not to do, if possible.

Can anyone please help me with a formula (an array formula perhaps?) that
will do what I want in one step.

Many thanks.

On Wed, 9 Oct 2013 00:14:44 +0100, "Victor Delta" wrote:

I have an Excel spreadsheet that contains two worksheets. The first contains
a set of data records - one row per record.

In the second worksheet, I would like to create a column which lists, in
numerical order, the row numbers of the first worksheet where the data in
column C is equal to the word 'HOME'.

I've thought of a way of doing this but it involves adding an extra hidden
column to the first worksheet which I would prefer not to do, if possible.

Can anyone please help me with a formula (an array formula perhaps?) that
will do what I want in one step.

Many thanks.


This formula must be **array-entered**:

Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(IND IRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))," ")

Change all of the 100's to the highest row necessary. You could reference the entire row (Sheet1!$C:$C), but it will run more slowly
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

"Ron Rosenfeld" wrote in message
...

On Wed, 9 Oct 2013 00:14:44 +0100, "Victor Delta" wrote:

I have an Excel spreadsheet that contains two worksheets. The first
contains
a set of data records - one row per record.

In the second worksheet, I would like to create a column which lists, in
numerical order, the row numbers of the first worksheet where the data in
column C is equal to the word 'HOME'.

I've thought of a way of doing this but it involves adding an extra hidden
column to the first worksheet which I would prefer not to do, if possible.

Can anyone please help me with a formula (an array formula perhaps?) that
will do what I want in one step.

Many thanks.


This formula must be **array-entered**:

Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(IND IRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))," ")

Change all of the 100's to the highest row necessary. You could reference
the entire row (Sheet1!$C:$C), but it will run more slowly
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

-----------------------------

Ron

Very many thanks for this. Have entered the formula as suggested by each
cell shows #NAME? - Have I done something silly?

V

Hi Victor,

Am Wed, 9 Oct 2013 09:14:29 +0100 schrieb Victor Delta:

Very many thanks for this. Have entered the formula as suggested by each
cell shows #NAME? - Have I done something silly?

for me it works.
You can also try in Sheet2 A1:
=IFERROR(SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($1:$ 100)),ROW(A1)),"")
Enter this formula also with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

"Victor Delta" wrote:
"Ron Rosenfeld" wrote:
This formula must be **array-entered**: Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(IND IRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))," ")
[....]
cell shows #NAME? - Have I done something silly?

Are you using Excel 2003 or earlier?

IFERROR is for Excel 2007 and later.

One alternative is to use ISERROR. Not pretty! The form would be:

=IF(ISERROR(SMALL(...)),"",SMALL(...))

Note that the SMALL expression would be calculated twice if there is no
error.

I have not reviewed your needs to see if there is a better alternative.

On Wed, 9 Oct 2013 09:14:29 +0100, "Victor Delta" wrote:



Very many thanks for this. Have entered the formula as suggested by each
cell shows #NAME? - Have I done something silly?

V

Possibly you have a typo, or you are using a version of Excel prior to 2007 that does not support IFERROR.

If that is the case, the following **array-entered** formula should work, as mentioned by joeu

=IF(ISERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW( INDIRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))), "",
SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(INDIRECT("1: 100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1)))

If that is the problem, then a variant of Hans formula should also work:

=IF(ISERROR(SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($ 1:$100)),ROW(A1))),
"",SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($1:$100)), ROW(A1)))

"joeu2004" wrote in message ...

"Victor Delta" wrote:
"Ron Rosenfeld" wrote:
This formula must be **array-entered**: Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(IND IRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))," ")
[....]
cell shows #NAME? - Have I done something silly?

Are you using Excel 2003 or earlier?

IFERROR is for Excel 2007 and later.

One alternative is to use ISERROR. Not pretty! The form would be:

=IF(ISERROR(SMALL(...)),"",SMALL(...))

Note that the SMALL expression would be calculated twice if there is no
error.

I have not reviewed your needs to see if there is a better alternative.

--------------------------------

Very many thanks for that. Yes, you were absolutely right, I use Excel 2003
but had forgotten to mention it - apologies.

I used your iserror version of Ron's original formula and it works just
fine.

Thanks both again,

V

"Ron Rosenfeld" wrote in message
...

On Wed, 9 Oct 2013 09:14:29 +0100, "Victor Delta" wrote:



Very many thanks for this. Have entered the formula as suggested by each
cell shows #NAME? - Have I done something silly?

V

Possibly you have a typo, or you are using a version of Excel prior to 2007
that does not support IFERROR.

If that is the case, the following **array-entered** formula should work, as
mentioned by joeu

=IF(ISERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW( INDIRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))), "",
SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(INDIRECT("1: 100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1)))

If that is the problem, then a variant of Hans formula should also work:

=IF(ISERROR(SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($ 1:$100)),ROW(A1))),
"",SMALL(IF(Sheet1!C$1:C$100="HOME",ROW($1:$100)), ROW(A1)))

------------------------------

Ron

Many thanks for that.

V