| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! -- rerhart ------------------------------------------------------------------------ rerhart's Profile: http://www.excelforum.com/member.php...fo&userid=8138 View this thread: http://www.excelforum.com/showthread...hreadid=570905 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display list from one colum if value in other column matches x
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display list from one colum if value in other column matches x
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display list from one colum if value in other column matches x
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),IN DEX(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" wrote in message ... 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(Asse ts!$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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display list from one colum if value in other column matches x
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display list from one colum if value in other column matches x
You're welcome. Thanks for the feedback!
Biff "rerhart" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop down list shrinks as you go down column | Excel Discussion (Misc queries) | |||
| insert date | Excel Worksheet Functions | |||
| Formula to search for given term, and if not found in column to add it to list | Excel Worksheet Functions | |||
| Change a Column list into multiple rows & columns | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linear Mode
