Help with an Excel formula please?

V

Victor Delta

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

Ron Rosenfeld

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.
Click to expand...

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

Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(INDIRECT("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.
 
V

Victor Delta

"Ron Rosenfeld" wrote in message

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.
Click to expand...

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

Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(INDIRECT("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
 
C

Claus Busch

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?
Click to expand...

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

joeu2004

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

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

Ron Rosenfeld

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

V
Click to expand...

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)))
 
V

Victor Delta

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

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
 
V

Victor Delta

"Ron Rosenfeld" wrote in message

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

V
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dealing with the error: unable to filter on excel hc home school 0
Mail Merge Help 1
Cumulative formula for word table 0
Need Help with a VBA subroutine 0
Merging from an Excel file with graphics 1
Copy addition formula of non-consecutive horizontal cells 2
Autonumber field in Excel that behaves the same as Access 0
Excel 2013: New row added automatically but first 2 columns Drop down lists not available... 0

Top