show unique locations or account # by month

any thoughts on this guys? thx u

In the attached, the "First Occurrence" is automated. That's what I think you're after. If not, please let me know. This did require a table -- also automated except for extending it into 2014 and beyond, which is highlighted in blue.

Hello bentleybob

I assume column AA list the accounts or what exactly is that showing?

That being said, i will need to show all unique locations by month to show the user whats "new" for that month for that "unique" location. In addition, column AI = ref# error.

thanks for your help...pls let me know sir.

Hello bentlybob

after review it again, i am assuming column AA shows the "# of unique" locations in a particular month?

for example, in 1-2012 there are 2 unique locations (cell AA2).

that is what i am somewhat hoping to achieve.

what i want to show:
for example, 1-2012 : list all unique locations (NOT the # of total locations), i would like to show what account # or address is solely *new* or *unique* that does not repeat for that month and list them by their account # or address.

hope that makes more sense? appreciate the help sir.

Column AA simply shows the row number where the month changes. It’s used in the formulas in Column V. But there aren’t 2 unique locations in 1-2012, there are 184! That’s almost half of them.

Column AI was a temporary check to see if my Column V results matched yours; you can delete it.

As for showing all unique locations by month, I guess the easiest thing to do is to use the filters. So if you wanted to see all the unique locations for, say February 2012, you would filter Column R for 2012, Column S for 2, and Column V for 1. If you truly want another place where only the unique locations are listed (so you don’t have to filter), that’s doable too, but it would be a very long list because over 40% of your line items are unique. If you MUST have it, I can do this (preferably in a separate tab), but I didn’t want to put that work into this if it wasn’t really needed.

BTW, you have three occurrences where the address in Column F matches but the Account in Column D doesn’t. Since I used the address as the key (as I think you did), you get a slightly different answer than if you had used the account number. I say this only because in your original post, you said you could use either.

hello icestationzbra

that is perfect.......80% of what i was hoping to achieve. your formula list the # of "unique" or "new" locations by month, which is awesome.

what i am looking for is to show the "list of accounts that are unique". For example, cell AA2 = 185 unique locations. would it be possible to show the 185 locations, their address in column F or account # in column D?

pls let me know; thanks for your help!!!

Originally Posted by icestationzbra

see if this is what you are looking for...

SIR YOU ARE AN ALLSTAR!!! WOW.....

Note: I do prefer the method with the dates but thank you with the bottom of my heart!

cheers, have a great weekend!