Display list from one colum if value in other column matches x

I have 2 sheets in workbook. On sheet1 I have values in 2 columns, such as:

Name Type
test1 A
test2 B
test3 A
test4 A
test5 B

On sheet2 I would like to summarize the information by Type, such as:

TypeA TypeB
test1 test2
test3 test5
test4

What formula can I configure on sheet2 to acquire this result?
Thanks!

See this screencap:

http://img104.imageshack.us/img104/8...reencapdg3.jpg

The easiest way to do this is to put the formula on the same sheet like I
did and then after you have the table completed cut and paste it to the
other sheet. That way you don't have to mess around with sheet names and
flipping between sheets as you write the formula.

Biff

"rerhart" <[email protected]> wrote in
message news:[email protected]...
>
> I have 2 sheets in workbook. On sheet1 I have values in 2 columns, such
> as:
>
> Name Type
> test1 A
> test2 B
> test3 A
> test4 A
> test5 B
>
> On sheet2 I would like to summarize the information by Type, such as:
>
> TypeA TypeB
> test1 test2
> test3 test5
> test4
>
> What formula can I configure on sheet2 to acquire this result?
> Thanks!
>
>
> --
> rerhart
> ------------------------------------------------------------------------
> rerhart's Profile:
> http://www.excelforum.com/member.php...fo&userid=8138
> View this thread: http://www.excelforum.com/showthread...hreadid=570905
>

Thanks, that works...How do I get rid of the #NUM?

A B
test1 test2
test3 test5
test4 #NUM!
#NUM! #NUM!
#NUM! #NUM!

You shouldn't get #NUM!. Did you see any in the screencap?

Post the EXACT formula you used. Post the formula from the very first cell
that returns "test1".

Biff

"rerhart" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks, that works...How do I get rid of the #NUM?
>
> A B
> test1 test2
> test3 test5
> test4 #NUM!
> #NUM! #NUM!
> #NUM! #NUM!
>
>
> --
> rerhart
> ------------------------------------------------------------------------
> rerhart's Profile:
> http://www.excelforum.com/member.php...fo&userid=8138
> View this thread: http://www.excelforum.com/showthread...hreadid=570905
>

Here's my actual/modified formula and I still get #NUM! in the blank cells.

{=IF(ROW($1:1)<=COUNTIF(Assets!$C$2:Assets!$C$300,A$1),INDEX(Assets!$A$2:Assets!$A$300,SMALL(IF(Assets!$C$2:Assets!$C$300=A$1,ROW(Assets!A$2:Assets!A$300)-ROW(Assets!A$2)+1),ROWS($1:1))),"")}

Ok......

=IF(ROW($1:1)...........

Should be:

=IF(ROWS($1:1)............

That'll take care of the #NUM!'s.

Tip: you don't need to repeat the sheet name in a range reference ---
Assets!$C$2:Assets!$C$300

Just use --- Assets!$C$2:$C$300

Also, --- ROW(Assets!A$2:Assets!A$300)-ROW(Assets!A$2)

You really don't need sheet names for these references. These references
don't actually refer to the ranges you see. They just return the number of
the row reference. For example, ROW(Assets!A$2) = ROW(2).
ROW(Assets!A$2:Assets!A$300) = ROW(2:300). This is a little confusing if you
don't understad how the formula works but you can take my word for it!

So, here's your formula cleaned up:

=IF(ROWS($1:1)<=COUNTIF(Assets!$C$2:$C$300,A$1),INDEX(Assets!$A$2:$A$300,SMALL(IF(Assets!$C$2:$C$300=A$1,ROW(A$2:A$300)-ROW(A$2)+1),ROWS($1:1))),"")

Don't forget, when you re-enter the edited formula it MUST be re-entered as
an array using the key combination of CTRL,SHIFT,ENTER.

Biff

"rerhart" <[email protected]> wrote in
message news:[email protected]...
>
> Here's my actual/modified formula and I still get #NUM! in the blank
> cells.
>
> {=IF(ROW($1:1)<=COUNTIF(Assets!$C$2:Assets!$C$300,A$1),INDEX(Assets!$A$2:Assets!$A$300,SMALL(IF(Assets!$C$2:Assets!$C$300=A$1,ROW(Assets!A$2:Assets!A$300)-ROW(Assets!A$2)+1),ROWS($1:1))),"")}
>
>
> --
> rerhart
> ------------------------------------------------------------------------
> rerhart's Profile:
> http://www.excelforum.com/member.php...fo&userid=8138
> View this thread: http://www.excelforum.com/showthread...hreadid=570905
>

Excellent! Thank you!

You're welcome. Thanks for the feedback!

Biff

"rerhart" <[email protected]> wrote in
message news:[email protected]...
>
> Excellent! Thank you!
>
>
> --
> rerhart
> ------------------------------------------------------------------------
> rerhart's Profile:
> http://www.excelforum.com/member.php...fo&userid=8138
> View this thread: http://www.excelforum.com/showthread...hreadid=570905
>