PDA

View Full Version : Solved: Advanced filter techinques?



OTWarrior
11-12-2008, 01:56 AM
This could also be an Access question (as I made the report using an Access query) but I am looking at the data currently in Excel, and also want to improve my excel knowledge.

I have a report which shows me the total number of bookings in a room for each day of the month, and shows me the count of how many are booked in there according to our database. There should only ever be one booking per room, or no bookings. The purpose of this report is to find the duplicate bookings

I have included some sample data so you get an idea of what the data kinda looks like.

The problem is I have 8500 rows (rooms) to sort through, and many of these rooms are either not in use (0) or booked correctly (1). And value greater than 1 is highlighted in yellow using conditional formatting.

Using an autofilter, I could only display all of the rooms that are greater than 1, but I can only do this for one column (day of the month), whereas I would want it to work accross all 31 columns (if any one of the 31 columns contains a number greater than 1 across the row, display that row).

I am thinking that an extra column with some kind of function could help me to filter it, but the only idea I have is to sum up the usage of the room, which doesn't help me if the room is 0 throughout all the month, but double booked for one week.

Any help would be massively appreciated.:help

Bob Phillips
11-12-2008, 02:14 AM
An extra column is the way to go.

Use a formula of

=COUNTIF(B2:AF2,">1")>1

and then filter on a value of TRUE

OTWarrior
11-12-2008, 02:40 AM
Thanks for the help Xld.

I just tried it, and it doesn't work if only one of the cells in the row is greater than one.

EG: (the first one should also be true, the others are correct)

1 1 1 1 2 1 1 1 1 1 1 1 0 0 0 0 0 0 0 1 1 1 False
1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 1 1 1 False
2 2 2 2 2 2 2 2 2 2 1 1 0 0 0 0 0 0 0 1 1 1 True

It's almost there, and should help my team to get rid of the major overlaps :). I did try match to try to sort this, but got the same result. I have double checked and the cells are all formatted as numbers.

Bob Phillips
11-12-2008, 04:08 AM
Sorry, my bad. Just change it to

=COUNTIF(B2:AF2,">1")>0

OTWarrior
11-12-2008, 04:26 AM
Perfect, thank you very much.

(Strange, I don't have the option to mark it as solved in Chrome, have to switch to IE)