Finding unique customer total of data for a year

Ok here is what I have -

Customers who purchased items over a year time span.  Each customer may have purchased multiple items per day. The customer may have also purchased the same item multiple times during the year.  What I want to know is the number of unique people who purchased item 104 over the year.

I have 50,000 transactions for the year and one to twenty two items per purchase. There are 1,700 total items and 15,000 individuals with unique id's.  I originally had the items in one excel cell but split them each into their own cell.

I even imported the data into access thinking I could use the unique individual id's in some way but I am stuck.

How can I find the unique number of people who purchased item 108?
LVL 9
Sean MeyerAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
I don't know how efficient it will be, but this formula works in your sample:

=COUNT(1/FREQUENCY(IF(ISNUMBER(SEARCH(","&A18&",",","&B1:B12&",")),IF(A1:A12<>"",MATCH(A1:A12,A1:A12,0))),ROW(A1:A12)-ROW(A1)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER.  and copied down.

With that many rows, it won't be very efficient, and I can't think of a way to make it more efficient, save perhaps a macro (udf), which is out of my scope, unfortunately.
0
 
NBVCCommented:
Try:

=COUNT(1/FREQUENCY(IF(B1:B50000="104",IF(A1:A50000<>"",MATCH(A1:A50000,A1:A50000,0))),ROW(A1:A50000)-ROW(A1)+1))

confirmed with CTRL+SHIFT+ENTER where column A contains customer names, and column B the item number.

Note: if the item number is a true number format, then remove the quotes from around 104 in the formula:
0
 
Rey Obrero (Capricorn1)Commented:
what are the names of your tables?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
NBVCCommented:
Another option if you have 2007 or later version:

=SUMPRODUCT((B1:B50000="104")*(A1:A50000<>"")/COUNTIFS(A1:A50000,A1:A50000&"",B1:B50000,EB1:B50000&""))

confirmed regularly (ENTER only)
0
 
Sean MeyerAuthor Commented:
I have split the product into Columns AG to BH.  Otherwise I can have them all comma deliminated in Column V.

I only have one table which is a direct import from the excel.  It has been a while since I have worked with Access and I can't even remember how to make a query which gives unique customer totals.
0
 
NBVCCommented:
I am not sure I follow... and this is not the Access forum....
0
 
Sean MeyerAuthor Commented:
Ok... I have customer id in column C.  I have items purchased in columns AG to BH.  Each one listed seperately.  Most purchases are one to 7 items but some people buy more items in one purchase.

Each row contains a different set of purchased.
0
 
NBVCCommented:
So you want to count the number of unique items per row (i.e for each customer)?

I am a bit confused as to what we are actually counting (based on what criteria).
0
 
Sean MeyerAuthor Commented:
I am looking to find the number of customers who purchased an item.

So if item 108 = bucket. And customers 7, 88 & 577 each purchased a bucket I want my total to be 3.

I still want my total to be three if customer 7 purchased two of the item on June 1st, purchased three more on July 1st and customer 88 and 577 each purchased one bucket at different times.

So the number being sought is the unique number of customers who purchased each inventory item.
0
 
NBVCCommented:
I think it would be easier to follow if you could post a sample file.  Showing your layout and a sample of the input/output desired.
0
 
Sean MeyerAuthor Commented:
Example of data and output.

The customer id is in column A.
The aggregate purchases are in column B
I have broken the aggregate into individual items in their own columns thinking that may be easier to work with but I have no idea how to get the type of data I want.

There are 1,400 plus product types.  There are 50,000+ purchases of over 300,000 separate items.  I want to know the number of unique people who purchased each type of product.
example.xlsx
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
it will be best to import the excel file to Access table, parse the item column and create new records for each of the items purchase.

then, a simple Total query with give what you want.
0
 
Sean MeyerAuthor Commented:
NB_VC,
Where do I put your formula into the sample document?  I tried to paste it in  a few locations and all I got was "0"  I am trying to follow the logic and it is beyond me.  What is the formula doing in English ?   I am hoping that will let me use it better :)

capricorn1,
I have worked with access in the past but it has been years.  I have the excel sheet imported into excel.  What do you mean by "parse the item column"  Is that the same as split each entry into its own cell?  I did that in excel and then did the import.  

What do you mean by "create new records for each of the items purchased"?  Create a table with just item numbers?   How would I form the query so that it does not return duplicates?  Can you give an example based on the small example I did?

GAH!  Thought I submitted this Friday ... any help appreciated
0
 
NBVCConnect With a Mentor Commented:
The formula is meant to go into B18.  After you enter it, you have to confirm it by holding the CTRL and SHIFT keys down, then press ENTER.  You will see { } brackets appear around it.

Then you can copy it down the column.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this db

run query1
and query2
Database2.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.