Reply to: Excel filtering

[engineer]

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

For Access it is not even necessary to know SQL phrases. Some graphical mouse movement in the DDE window is all.

[DaveB]

Sorted, many thanks.

[BoredBloke]

I have a rough bit of VBA which does what I think you need - PM me if you want it

[scooterscot]

Thanks for that Scooter, almost there. The formula you gave doesnt quite work though, although it may just be me being dense.

=IF(VLOOKUP($A$1,A5:EE316,2,FALSE)='x',TRUE,FALSE) doesn't work, but giving it a hard coded cell location does.

=IF(VLOOKUP(A5,A5:EE316,3,FALSE)="x",TRUE,FALSE)

So either $A$1 isnt correctly evaluating to the active cell in that range or I've done something wrong somewhere in setting it up. Any ideas?

You'll need $ around the array so A5:EE316 should look like $A$5:$EE$316

Also $A$1 should not change so: =IF(VLOOKUP(A5,A5:EE316,3,FALSE)="x",TRUE,FALSE) should be:

=IF(VLOOKUP($A$1,$A$5:$EE$316,3,FALSE)="x",TRUE,FA LSE)

$A$1 should be a drop down menu if the validation is working.

Trial and error will get you there in the end.

[Spacecadet]

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

<snip>
No macro, no code, no problem :O)

Why do i see everything as a challenge?
Using a few things i've done before, I've knocked up an example spreadsheet that puts a llist of users, without blanks, into a column depending on the name of a function being entered into a cell.

All very simple formula, no VBA

Let me know if you want it

[DaveB]

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 are interested in 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)

Thanks for that Scooter, almost there. The formula you gave doesnt quite work though, although it may just be me being dense.

=IF(VLOOKUP($A$1,A5:EE316,2,FALSE)='x',TRUE,FALSE) doesn't work, but giving it a hard coded cell location does.

=IF(VLOOKUP(A5,A5:EE316,3,FALSE)="x",TRUE,FALSE)

So either $A$1 isnt correctly evaluating to the active cell in that range or I've done something wrong somewhere in setting it up. Any ideas?

[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

[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!

[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.

[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)

[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.

[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

[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.

[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

[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?