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
|