show unique locations or account # by month

[jw01]

I have a list of accounts ~6500 rows of data.

it shows accounts listed by "account # - column D" and "address - column F".

I require a formula (not pivot table) In cell Z2 i want to show "unique" list of locations by EACH MONTH that occur only once, so i can calculate their revenue. For example, ABC Inc may be serviced 3 times in a month, however i want to show the value for the "first" service that occured for that month.

THerefore, my criterias are
Month = column S
Category = "auto" column T
Unique address = column F or use column D (account #)

i have been stuck on this and dont know where to go. any help will be truly appreciated....thanking you in advance.

[jw01]

any thoughts on this guys? thx u

[bentleybob]

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.

[jw01]

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.

[jw01]

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.

[bentleybob]

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.

[icestationzbra]

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

[jw01]

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!!!

[jw01]

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!

[icestationzbra]

hi there,

i was dismayed by the performance of the file that i had provided earlier, so i attempted a MS Query solution to get what you want. the performance has improved tremendously.

place the attached file in "C:\Downloads\". you can change this path later. then, open the file. upon opening, it will ask you to enable Data Connections, go ahead and enable that. on Summary tab of the attached file, there is a Smart Table - that is the output of the MS Query.

i have checked the results for a couple of periods and they seem to be correct.

in case you want to view / edit the query, here is how you do it.

click anywhere within the Smart Table and punch ALT > A > O. you should see Workbook Connections window. highlight the one connection displayed and click on Properties. in the Connection Properties window, you will observe that the Smart Table refreshes every minute (Usage tab). now, click on Definitions tab and observe the Command Text block. copy all the contents into a Notepad to view.

SELECT db.Account, db.Year, db.Month, db.Category, Sum(db.`Net Value`) AS `Sum of Net Value`
FROM `C:\Downloads\jw01_926917_02jun13_01.xlsx`.db db
WHERE db.Category='AUTO'
GROUP BY db.Account, db.Year, db.Month, db.Category
ORDER BY db.Year, db.Month, db.Account

you can change the path of the file here. the quote being used above is back-quote or accent-grave (the character below tilde on a US keyboard).