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 |
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 |
All times are GMT +1. The time now is 07:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com