Hi,

I am trying to do something which is pretty easy conceptually, and can
be done pretty easily with a macro, but I'd like to do it in a single-
cell formula.

In Sheet1, say I have two columns: Names and Group. Let's say that
there are 10 groups total, and 500 Names, but Names can repeat, and
there can be the same name within the same Group.

What I want to do in Sheet2 is have, say 'Group 1' in cell B2, and
then below it, list each name that belongs to Group 1 below it. If
there is a name that is in Group 1 more than once, I want to list it
as many times as it occurs (but if it's easier to list just once, that
could work also).

The column with the Names is alphabetical, so after you're finished
with Name1, it won't be repeated after Name2 begins. Group 1 is in no
order other than to correspond with the name.

I think it's a function of combining OFFSET, MATCH, INDEX, and/or
CHOOSE, but not really sure what to do after I get the first one.

Thanks!

Brett

in B3:

=IF(ISERROR(SMALL(IF(Sheet1!$B$2:$B$20=B2,ROW(Shee t1!$B$2:$B$20),""),ROW($A1))),"",INDEX(Sheet1!A$2: A$20,N(SMALL(IF(Sheet1!$B$2:$B$20=B2,ROW(Sheet1!$B $2:$B$20),""),ROW($A1)))))

Enter with Ctrl+Shift+Enter and copy down

Assumes Sheet1, column B contains text such as "Group 1" i.e. same as
content of B2 in Sheet2. If not, change B2 to contain apprpriate match data.

" wrote:

Hi,

I am trying to do something which is pretty easy conceptually, and can
be done pretty easily with a macro, but I'd like to do it in a single-
cell formula.

In Sheet1, say I have two columns: Names and Group. Let's say that
there are 10 groups total, and 500 Names, but Names can repeat, and
there can be the same name within the same Group.

What I want to do in Sheet2 is have, say 'Group 1' in cell B2, and
then below it, list each name that belongs to Group 1 below it. If
there is a name that is in Group 1 more than once, I want to list it
as many times as it occurs (but if it's easier to list just once, that
could work also).

The column with the Names is alphabetical, so after you're finished
with Name1, it won't be repeated after Name2 begins. Group 1 is in no
order other than to correspond with the name.

I think it's a function of combining OFFSET, MATCH, INDEX, and/or
CHOOSE, but not really sure what to do after I get the first one.

Thanks!

Brett

Wow, this is great! It works nearly perfectly, but for some reason,
it skips the first few Names - I think it's a function of the $A1
reference you have in there, but I'm not really sure what's going on
in that formula. In Sheet1, the data starts in Row 4, and I am trying
to start the data in Row 3 of Sheet2, with B2 in Sheet2 being what I'm
searching for in the Group column. Any thoughts?

Thanks again!

Just had to start my range in row 1 - works perfectly - thansk again!!