Sort by Location

[ecohen1]

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

 

[DOR]

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