Solved

Finding unique customer total of data for a year

Posted on 2013-01-31
15
218 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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 120

Assisted Solution

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

run query1
and query2
Database2.accdb
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

832 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