Excel filtering

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

 

[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

 

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

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

Trial and error will get you there in the end.

 

[BoredBloke]

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

 

[DaveB]

Sorted, many thanks.

 

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