Function for VERY advanced Filter- criteria in two columns

[synses]

Hi,
I have a spreadsheet with 3 years of WRITING (scores) data for hundreds of schools, and I have a spreadsheet with the identifying info for only 60 TARGET schools; I only need the WRITING (scores) data for the 3 years for the TARGET schools, but can't figure out how to filter efficiently, which is important because I wil have to do the same thing for Reading, Math and Science scores....

How do I filter the WRITING sheet so that only the data for the TARGET schools' scores shows up? I couldn't attach the spreadsheet....it is 1.13mb...

any help would be greatly apprecaited,
Synthia

[tlafferty]

A fast way to do this is to use a helper column which will return true/false. Put the names of your target schools in an unused sheet in the workbook. Name the range TargetSchools. Assuming the names of your target schools in scores sheet appear in the A column starting at cell A1, put the following formula in the helper column:
=NOT(ISERROR(VLOOKUP(A1,TargetSchools,1,FALSE)))

This will give you TRUE/FALSE. Filter on TRUE to get the schools which appear in your target list. Filter on writing for writing scores, etc.

[Pete_UK]

If you list your target schools somewhere (say X1:X60, and with column A containing your "hundreds of schools"), then you could use a helper column with this formula:

=IFERROR(MATCH(A2,X$1:X$60,0),"")

Then you could filter using this column with a setting of non-blank. An alternative formula is this:

=IF(ISNA(MATCH(A2,X$1:X$60,0)),"","Y")

and then you could just filter for the Y values in this column.

Hope this helps.

Pete

[synses]

Your answer sounds good and simple I will try and let you know - thank you very much! I might need a s-l-o-w-e-r explanation for "Filter on TRUE to get the schools which appear in your target list." (that is how crude of an Excel user I am!) but will try to figure it out.
THANKS!!!
Synthia

[synses]

Thanks, I am a very inexperienced but highly adaptive user, I am going to try these and may need a little bit more help understanding exactly how to do some things I guess most people know, like
use a helper column" (do you add a column? to what sheet)
"with this formula: =IFERROR(MATCH(A2,X$1:X$60,0),"")" (where do you put that function?)
"Then you could filter using this column with a setting of non-blank." (how?)
"you could just filter for the Y values in this column." (how do you do that?)
but will try to do it and beg for more help if I get stuck.
Thanks,
Synthia

[FDibbins]

- a helper column is just any column you use, to put formulas in that will assist you to do what you need to do. you can use them for reference purposes in vlookup() and other functions, there is nothing mysterious about them
- -=IFERROR(MATCH(A2,X$1:X$60,0),"") if made up of 2 parts. the 1st part is the MATCH(A2,X$1:X$60,0) part, and what that does is looks to "match" the contents of A2 in the range X$1:X$60, with 0 being an exact match. you could alos use 1 for less than A2's value, or -1 for greater than A2's value. the 2nd part =iserror( just checks to see if the match() function returns an error (in other words it couldnt find an exact match). if it does not find the match, it returns a null value - the "" part
- to use filters, in the home tab, on the far right, select "filters" It will put a series of "pull-downs" at the top of each column. in the helper column, click on the pull-down, unselect "all" ane select non-blanks
- same principle as above, but he has used a slightly different formula that will return a "Y" when it finds it. the IF(ISNA( is just another variation on IFERROR, and would be used by people using excel versions lower than 2007, as they dont have the ISERROR function.

Hope that helps explain some of what Pete suggested for you?

[synses]

Thanks for your patience and explanation.
To create an accurate helper column I need to combine the contents of cells in two columns into a new column (on the "TargetSchools" sheet and on the Scores sheet) because there are two columns with identifying info in them, no one column is unique, but the two columns combined are, so I posted this inquiry and am pasting it here as well:
In $D$2:$D$3103 there are 2 digit numbers stored as text. "50" or "13" or "06"
In $F$2:$F$3103 there are 4 digits numbers stored as text. (like "0965" but hundreds of variations)

I need $A2:$A$3103 to be what is in $D$2:$D$3103 AND what is in $F$2:$F$3103 without any spaces, etc. (like 500965)

See, the 4 digit numbers (school ID#S) are unique ONLY to the two digit numbers (district ID#s), so if I combine them, there will be one unique identifier per school/per row.

I may get an answer from the post, but just wanted to let you know where I am beginning.

OR, can the function be written to match the two columns...?
Feeling ignorant and missing the bliss in it,
Synthia
Thanks,
Synthia

[Pete_UK]

To join the contents of two cells together (also called "concatenation") you can just use this formula in A2:

=D2&F2

and then copy down - simple !!

A quick way of copying the formula down if you have contiguous data (sorry about the jargon - it means no gaps in the data) in column B, then select A2 and double-click the fill handle, which is the small black square in the bottom right corner of the cursor.

Hope this helps.

Pete

[synses]

HI Pete, Mr. Dibbins and Thomas,
Found copy/paste errors I had to fix, have not tried the final filter yet, and must go to sleep, and start over tomorrow, but can't retire without a huge thanks to each of you, I am very lucky to have generous your help!
Gratefully,
Synthia

[synses]

I tried for a while, got overwhelmed with other things, regret giving up, now I need the reports yesterday, and I still can't get this to work. I understand everything, and appreciate your help but also am asking for one more solution:
The helper column is in sheet 1, b1:b62
The 100s of schools are on sheet 2, rows 2 thru 444 (just one county- I have three counties)
The identifier column wiht numbers matching the helper column is column D,
I wrote [code =IFERROR(MATCH(D2,Sheet1!B$1:B$62,0),"") /code]
and put it in D2, before the identifier number for the school that sits in that row. (is that where I am supposed to put the code/function..?)
Because the spreadsheet returns an error, suggests I use this:[code =IFERROR(MATCH(D2,Sheet1!B$1:B$62,0),"")*130041/code]
and it gives zeroes all the way down the column, no matches.
I think I am not putting the function in the right place?
Thanks for any help you can offer!
Synthia

[synses]

EUREKA this is solved thank you to ALL of you that persisted and patientlky walked me through this - I am much more limited than I thought! Anybody have suggestions for REALLY good DVDs to teach this kind of stuff? I can't just keep coming back and asking.....
Anyway, it worked!!!!I can do tons of work with my long long lists being efficiently filtered now!!!!
Thank you very genuinely, please don't give up helping others, it is incredibly valuable!!
Synthia

[FDibbins]

glad we could help, and of course you can keep coming back here. for most of us, this is probably the best way to learn new stuff

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[synses]

Thanks, I appreciate it, the problem TRULY is solved and COULD NOT have been solved without your help. I had seen the instructions on your messages about marking the problem solved and tried to do so but was probably too rushed to see the way to "click solved" using the advanced mode and could not find it. I did add to the reputations - as many times as allowed - because this has been very beneficial. I have a whole different problem now, with generating reports from spreadsheets that meet my director's criteria, but am so rushed can't even take the time to post it!
But I HAD to take the time to say thanks again- I know how valuable your time and talent are and am very grateful!
Synthia

[FDibbins]

thank you so very much for the kind words, they are always appreciated, and thanks for the reps.

i will close this thread for you