I want to filter out so I only have rows that have the same street name and the same st. no.
An example of what I am talking about is highlighted in yellow

How do I do this?

 

Are you trying to only display that information or just organize it like that? If you are trying to just organize it Tools>Sort and choose the columns to sort by. If you're trying to only display that info, then maybe a query?

 

i want to display ONLY that info. I already know how to sort, but I dont want any 'uniques' I only want rows that have the same st. no 2+ the same.. not sure if I am explaining myself correctly

 

Data > Filter > Advanced Filter



I learned to that in my computer class, and I already forgot.

 

or.....

Data > Filter > Autofilter > pick the two that you are looking for from the dropdown in each column

 

I am not looking for only TWO... I want it to display ALL the rows that have the same street name and st. no.
This list is 11000+ rows long... so using an autofilter would be tedious

 

two, as in the two parameters you want to set - the 1) street name and 2) street number

 

yes, but I want it to display ALL rows that share the same number same st. name.

perhaps I'm not explaining what I want...I want the spreadsheet to display all the GROUPS of addresses. I dont want any unique addresses.

 

ha, it was your picture that made me think something different.... so you want it to group all addresses together like what you've highlighted.

Perhaps a pivot table would work best.

 

omg my brain is about to explode

 

If you're basically looking to eliminate rows where that address is the only time it appears in the list, then I don't think you can do that in excel without it being a tedious process.

 

yes thats exactly what I want... there's gotta be a way!

if i get fired for not being able to do it...i'm blaming you guys.

 

I have an idea, but I'm not sure how well it will work for 11k rows...

 

First sort them like you have now.
In a separate column write an "if" formula like:
=if(f7=f6,if(g7=g6,1,0),0)
This will give a 1 in every column with a unique street name. Then sort by that column, so all the ones are at the top and you can separate them from the others.

 

Isn't this why databases were invented?

 

First sort them so that they are grouped together appropriately...

then use the "OR" formula (formulas > logical > OR) in a blank column and set it so that the street number must equal the cell below it OR the cell above it...

This will return a value of FALSE for every row that is unique. Then just sort by that column and delete the rows with FALSE in them.

 

but this is one of those borderline projects that is probably easier to do in excel anyway.

 

Prob with this formula is you'll lose the first row that is the same too (f7) even though it's not unique, it isn't the same as the one above ...

You'd be much better off importing this into Access and run a quick query. It'll be quicker than trying to get excel to do the same thing

 

BRILLIANT!
thanks!!!!

ccanniz11=Official AZ Excel pro!

 

Nice ...

 

whoa there!! keep that on the DL

It took me a while to understand what you were asking for, but I finally got it.... good thing I don't have any work to do.


always glad to help out

 

Just ran through it quickly in my head- I was thinking "groups" since that was what he mentioned, forgot about the solo rows.