extrapolate rows based on criteria

Hi there! Wondering if this is possible from within excel:

I have a large spreadsheet of around 7000 rows and 12 columns/headers. It is a spread of real estate/properties and each row is a different property. Each column is criteria such as "address", "bedrooms", "bath", "county", etc.

What I would like to do is pull out/select specific rows based on criteria found such as: I want to select ONLY the properties that are in the following cities, with this many bedrooms, and this many bathrooms.

would love any help! thanks =)

Autofilter should do that for you.

hmm, sounds simple enough! let me investigate

thank you!!

Select Data > Filter > AutoFilter (in 2003 at least) and each column in the header row will become a list box where you can select a value.
The sheet will then only display rows where the value in that column matched your chosen value.
You can then filter on another column to narrow your search

wow...i love Excel!

thank you!!

hey gents, one more quick question....i have a list of zip codes in excel (each row a different zip code). there are thousands of them. i could use autofilter one by one on my main sheet to show only the zip codes on the list but it would take forever. is there any clever way to enter in all of those zip codes in the autofilter?

thanks!!

Sorry for being unclear!

So I have a main sheet of properties as described above.
Each row a new property
Each column a new criteria about TE property, such as zip code county etc

Additionally I have a separate spreadsheet that has a list of zip codes - each row a new zip code

My goal is to only show the properties that match the zip codes listed that match the separate sheet

I successfully used auto filter for the other categories such as county type of property etc
But since there are so many zip codes it is not do easy

So the zip code in the first sheet (The property one) must also appear in
the list of zip codes in the second sheet in order to be displayed?

Have I got that right? If so then it just got a little more complicated.
It can be done, and without too much trouble, but autofilter isn't
up to the task.

A small userform with some VBA could do the job quite easily and
I'd be happy to draft one up for you, but you may want to upload
both files (or sample files that have the same layout) so that I can
write it properly.

OK - I've made a start on it but it's half past midnight here so I'm going to bed soon.
I'll work on it again in the morning and hopefully get something back to you tomorrow.

thank you so much!

is there anything i can do to help?

After spending a few hours messing about with arrays, listboxes and comboboxes and trying to be clever, I realised I was missing something a bit obvious
All you need to do, is add a derived column to your spreadsheet that shows if the zip code appears in the other list (Yes or No) and then filter on that.

I had to mess about with your sheet a little as I only have 2003, and in 2003 format the file is huge so I can't upload it.
I Copied your list of zip codes from the pivot table to column B of Sheet2

Basically what you need to do is this...

In column M (after Phone) create a header row of "In Zip List" (or similar)
In M2 enter the following formula - =IF(ISNA(VLOOKUP(F2,Sheet2!B:B,1,FALSE)),"No","Yes")
Then fill down that formula to the rest of the rows.

What it does, is check to see if the zip code in the current row appears anywhere in the list of zips on sheet2.
If it finds the zip it populates the cell with a Y otherwise an N.

All you need to do now is use autofilter and selct the Ys to get all the properties in the zipcodes your investor was interested in.

Sorry it took so long - I really wanted to do something clever with VBA

wow! you are impressive.

I completely get the concept but for some reason the formula is not working for me--it seems to want to pull up a browse window box.

i think it just can't find the sheet we are looking for (the zip code list)

currently, i have the spreadsheet with sheet 1 titled: "Zip Codes by State" on sheet 1 and sheet 2 titled: "Properties"

how can i call the sheet 1 for the formula?

thank you again!!

Just change the formula to =IF(ISNA(VLOOKUP(F2,'Zip Codes by State'!B:B,1,FALSE)),"No","Yes")
Don't forget the single quotes :-)

I think you did it!

one weird quirk, using it on another spreadsheet (setup identically just different file name and different properties) and the formula returns some "blanks" in the In Zip Code/formula column. any ideas?

You're welcome.

Ok, so no idea but for some reason the fields are blank because the formula is not filling down! It skips random rows for some reason. im just doing the "double click" on the fill down area on the first cell to autofill down. why is it skipping certain rows!

and you thought you were done with me.... =)

Note: if i manually drag down the fill down it works perfectly

Not a clue.
After putting the formula in the top row, I just highlight it, and all the rows below where I want the formula and then press <Ctrl+D>
I don't know anything about a "fill down area" - either it's not in 2003 or I'm just not particularly observant

hey Grul (or anybody else!), for some reason the formula is not working on a new spreadsheet. i have checked everything that i can think to check but for some reason the cell only comes back with "No" even though the zip codes match in some instances.

i have uploaded the spreadsheet i am working on: http://min.us/lIjzFoY7qiKSP

thank you!!

anybody? really stumped!

Been really busy the last week or so and only just found the time to reply.
Unfotunately the link you provided doesn't seem to be working for me.

sorry! here you go: http://ge.tt/9e3j3gS/v/0?c

Well, a bit of VB code showed me that there were in fact over 500 zip codes from the zip code sheet in the property sheet.
I tried copying the zip codes to a different sheet and looking up on that as I did in the beginning - nothing.
I tried saving it as xls instead of xlsx (also like I did with the first sheet) - nothing again. I really have no idea why the new sheet doesn't work.
It all seems to be exactly the same as the first sheet (that works) but it's not returning anything.....
I'm afraid i can't think what the problem could be. Maybe someone else might have some ideas.