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

[rerhart]

I have 2 sheets in workbook. On sheet1 I have values in 2 columns, suc
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

 

[Biff]

See this screencap:

http://img104.imageshack.us/img104/8726/screencapdg3.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]

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

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

 

[Biff]

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]

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))),"")}

 

[Biff]

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]

Excellent! Thank you!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff