Originally posted by Fred Bloggs
View Post
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Reply to: Excel filtering
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "Excel filtering"
Collapse
-
-
I have a rough bit of VBA which does what I think you need - PM me if you want it
Leave a comment:
-
Originally posted by DaveB View PostThanks 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.
Leave a comment:
-
Originally posted by scooterscot View Post1) Select the cell that would be the origin of the matrix
<snip>
No macro, no code, no problem :O)
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
Leave a comment:
-
Originally posted by scooterscot View Post1) 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?
Leave a comment:
-
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
Leave a comment:
-
Export it to Access, even a numpty like me can do what you want then with just a very rudimentary understanding of SQL queries!
Leave a comment:
-
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.
Leave a comment:
-
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)Last edited by scooterscot; 20 June 2008, 09:08.
Leave a comment:
-
Originally posted by DaveB View PostI'll take it as a no and tell the client to sort it themselves
These allow you to construct 'report-like' tables from a list of data.
They may be able to do what you want.
Leave a comment:
-
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
Leave a comment:
-
Originally posted by DaveB View PostI'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?
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.
Leave a comment:
-
Originally posted by TheBigYinJames View PostI'm lazy, I'd knock up something in VBA. Pivot tables perhaps?
It could be done using formula but probably quicker to bash out some VBA
Leave a comment:
-
Originally posted by DaveB View PostI've got a large excel spread sheet that I need to filter.
Does anyone know a way you can do this?
Leave a comment:
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: