Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)...

The formula referenced in the article below works fine. Since the result
set can be an array up to the same size as the list, you will need to copy
the formula into the same number of rows as the list (i.e. if you have 500
rows in your list, the formula should reside in 500 rows otherwise you may
truncate your result).

Place the formula in the first row of where you want your resultant set
(remembering to use shift+ctrl+enter since it is an array formula) and then
autofill the formula into the remaining rows for the result set. This will
ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent rows.

Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range that
you are testing; replace $A$10 with the reference to the cell that has the
value you are testing for.

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Good luck!

John

Top of Page




"Gavin1969" wrote:


http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile: http://www.excelforum.com/member.php...o&userid=30551
View this thread: http://www.excelforum.com/showthread...hreadid=502001

Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)...

I use these types of formulas every day. This particular formula can be
shortened a little and also made a little more efficient.

The big difference between using these types of formulas versus pivot tables
and filters is that the formula method is dynamic!

Biff

"John M." wrote in message
...
Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)...

The formula referenced in the article below works fine. Since the result
set can be an array up to the same size as the list, you will need to copy
the formula into the same number of rows as the list (i.e. if you have 500
rows in your list, the formula should reside in 500 rows otherwise you may
truncate your result).

Place the formula in the first row of where you want your resultant set
(remembering to use shift+ctrl+enter since it is an array formula) and
then
autofill the formula into the remaining rows for the result set. This
will
ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent
rows.

Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range
that
you are testing; replace $A$10 with the reference to the cell that has the
value you are testing for.

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Good luck!

John

Top of Page




"Gavin1969" wrote:


http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile:
http://www.excelforum.com/member.php...o&userid=30551
View this thread:
http://www.excelforum.com/showthread...hreadid=502001