Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Finding unique customer total of data for a year

Posted on 2013-01-31
Medium Priority
235 Views
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
Question by:Sean Meyer
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 5
• 3

LVL 23

Expert Comment

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

ID: 38841091
what are the names of your tables?
0

LVL 23

Expert Comment

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

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

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

LVL 9

Author Comment

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

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

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

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

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

NBVC earned 1000 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

Rey Obrero (Capricorn1) earned 1000 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

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

NBVC earned 1000 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

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

run query1
and query2
Database2.accdb
0

## Featured Post

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last weekâ€™s ManageEngine webinar, where attendees received a comprehensive look at the maâ€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
###### Suggested Courses
Course of the Month6 days, 17 hours left to enroll