Excel filtering

[DaveB]

I've got a large excel spread sheet that I need to filter.

The spread sheet is a matrix - application functions listed by row and user id's by column with an X in the cells where there is an intersection.

What I want to be able to do is select the function and filter for all the columns that have an X in them in that row. that is Select a function and see a record of all the users with access to it.

Normally you can do it with the auto filter, but that works the other way around ( Select the column with the user name and filter for all the functions they have ). I can't transpose the sheet because there are more than 256 rows of data.

Does anyone know a way you can do this?

 

[TheBigYinJames]

I've got a large excel spread sheet that I need to filter.
Does anyone know a way you can do this?

I'm lazy, I'd knock up something in VBA. Pivot tables perhaps?

 

[Spacecadet]

I'm lazy, I'd knock up something in VBA. Pivot tables perhaps?

WHS

It could be done using formula but probably quicker to bash out some VBA

 

[DimPrawn]

I've got a large excel spread sheet that I need to filter.

The spread sheet is a matrix - application functions listed by row and user id's by column with an X in the cells where there is an intersection.

What I want to be able to do is select the function and filter for all the columns that have an X in them in that row. that is Select a function and see a record of all the users with access to it.

Normally you can do it with the auto filter, but that works the other way around ( Select the column with the user name and filter for all the functions they have ). I can't transpose the sheet because there are more than 256 rows of data.

Does anyone know a way you can do this?

This is why databases were invented to hold relationships in data and spreadsheets were invented to hold and perform maths on data.

Create a table called users another called functions and a third called users_functions.

Import the data into the database tables.

The SQL to find users with certain functions and functions by user becomes a trivial SELECT statement.

 

[DaveB]

My VBA skills are sketchy at best and I don't have access to Access or another SQL database to use to do it.

I'll take it as a no and tell the client to sort it themselves

 

[TheBigYinJames]

I'll take it as a no and tell the client to sort it themselves

Did you have a look at Pivot Tables?
These allow you to construct 'report-like' tables from a list of data.
They may be able to do what you want.

 

[scooterscot]

1) Select the cell that would be the origin of the matrix

2) Validate the column of functions as a list (Data > Validation)

3) Insert two rows above the users

4) the first row you have just inserted is going to show the function you have interested above each user. So if the selected function 'FUNC1' is selected all the users will have FUNC1 above them. '=$A$1' drag across all users.

5) the 2nd row you inserted type =IF(VLOOKUP($A$1,your array,2,FALSE)='x',TRUE,FALSE)

for the 2nd user it would be: =IF(VLOOKUP($A$1,your array,3,FALSE)='x',TRUE,FALSE)

=IF(VLOOKUP($A$1,your array,4,FALSE)='x',TRUE,FALSE)

=IF(VLOOKUP($A$1,your array,5,FALSE)='x',TRUE,FALSE)

and so on..

Now each user will have TRUE or FALSE if the function matches. You could use some form of conditional formatting to make this more obvious.

Better still you can now transpose the rows with USER and TRUE or FALSE and filter as normal.



No macro, no code, no problem :O)

 

[thunderlizard]

I would agree with the Prawn, or something similar. All the sensible options involve reshaping your data, probably into a table with 3 columns: user; function; yes/no. Then you'd be able to use the Excel autofilter.

 

[Fred Bloggs]

Export it to Access, even a numpty like me can do what you want then with just a very rudimentary understanding of SQL queries!

 

[NickFitz]

Definitely something that should be done with a RDBMS, but you may be able to garner some Brownie points by using scooterscot's solution whilst explaining that to them