Solved

Finding unique customer total of data for a year

Posted on 2013-01-31
15
202 Views
Last Modified: 2013-03-04
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?
0
Comment
Question by:Sean Meyer
  • 7
  • 5
  • 3
15 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38841072
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38841091
what are the names of your tables?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38841098
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
 
LVL 9

Author Comment

by:Sean Meyer
ID: 38841152
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
 
LVL 23

Expert Comment

by:NBVC
ID: 38841211
I am not sure I follow... and this is not the Access forum....
0
 
LVL 9

Author Comment

by:Sean Meyer
ID: 38841346
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
 
LVL 23

Expert Comment

by:NBVC
ID: 38841378
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Author Comment

by:Sean Meyer
ID: 38841492
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
 
LVL 23

Expert Comment

by:NBVC
ID: 38841506
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
 
LVL 9

Author Comment

by:Sean Meyer
ID: 38841926
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
 
LVL 23

Accepted Solution

by:
NBVC earned 250 total points
ID: 38843472
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 38843641
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
 
LVL 9

Author Comment

by:Sean Meyer
ID: 38851422
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
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 total points
ID: 38852086
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 38855243
test this db

run query1
and query2
Database2.accdb
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now