Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort by Location
Hi, I have a column "Request" and "Room": Request Room 2005080740 9 2005081491 1 2005081635 29 2005081635 29 2005081492 14 2005081636 1 2005081636 1 2005081493 1 2005081493 1 2005080741 8 2005079468 14 2005081494 1 2005081494 1 2005079907 1 2005080742 9 2005080742 9 2005081637 1 2005081139 29 2005080315 1 2005081495 1 2005081496 1 2005080316 1 2005079908 1 2005081140 1 2005081141 1 2005080317 1 2005080317 1 ..... and I have like 3000 records and I don't know all the values inside my column "Room", how do I count the number of request by "Room". Thanks -- ecohen1 ------------------------------------------------------------------------ ecohen1's Profile: http://www.excelforum.com/member.php...o&userid=12988 View this thread: http://www.excelforum.com/showthread...hreadid=488744 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort by Location
Assuming Request and Room are columns A and B, enter the following
formulas in C2 through E2: C2: =IF(COUNTIF($B$2:B2,B2)=1,B2,"") D2: =IF(ROW()-ROW($1:$1)<=COUNT(C:C),SMALL(C:C,ROW()-ROW($1:$1)),"") E2: =IF(D2<"",COUNTIF(B:B,D2),"") C2 is a helper column, which can be hidden - we could dispense with it if you wish, but would need array formulas. D2 will have room no. in sequence, assuming all room numbers are numeric (if otherwise, please respond and we can provide a different approach) E2 will have the count. HTH Declan O'R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
Stock Location Sorting Problem | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |