Counting Invoices when there are duplicates

[SeafoodDutch]

I have a data set that has a listing of multiple invoices with the invoice number repeated because the data also had the product that was sold.  I need to generate a report, for each month of the year, that shows how many orders a customer has each month.

Report with formula that's not working.

[#2]

It's possible with dynamic arrays (REDUCE or even PIVOTBY, if you're fortunate to have it on insiders) or with a pivot table. Do you happen to have an anonymized sample workbook to share?

[#3]

@SeafoodDutch 

Unless I'm mistaken, it would appear that you're displaying the desired report, not the dataset from which it is to be generated. 

Unless the data is highly confidential--and if it is, perhaps you could create a copy without the actual names of products or customers--it would help us help you if you could post not just an image, but the actual workbook. Put it on OneDrive or GoogleDrive with a link here that grants access to it.

[#4]

@mathetes  

https://docs.google.com/spreadsheets/d/165W7eVIvqnd0k564R9oN2SoygsU7xJPR/edit?usp=drive_link&ouid=10...

Here is a sample file.

Thanks for your help!

[#5]

https://docs.google.com/spreadsheets/d/165W7eVIvqnd0k564R9oN2SoygsU7xJPR/edit?usp=drive_link&ouid=10...

Here is a sample file.

Thanks for your help!

[#6]

I receive an "Unauthorized Error 401" when clicking that link.

[#7]

@Patrick2788,

I just changed to restrictions.

Sorry about that.

[#8]

@SeafoodDutch 

Thank you. I've attached a pivot solution.

[#9]

@Patrick2788 

Thank you for your attempt.  This is not what I was looking for.

For instance.  Customer D for January, the pivot table shows 7.  The answer really is 2.  There were only two orders in January.  On the 2 orders, there were 7 items.

Any other thoughts?

[#10]

Hi @SeafoodDutch 

With PowerPivot, assuming you run Excel >/= 2013 on Windows. See attached file

[#11]

@SeafoodDutch 

In the attached version of the workbook, I added the data source to the data model, then used Distinct Count as summary function for the Document No. field.

Does that do what you want?

[#12]

Now that I am going into my second year with this data, I figured I would need to use Power Query, so this is going to work fantastic for me. Thank you!

[#13]

Not sure I undestand why you would need to use Power Query...

[#14]

@SeafoodDutch 

Do you mind sharing your PQ solution? I'm curious why you believe it's the way to go.

I agree with @L z.  - table the source and add to data model. Base the pivot off the data model and use distinct count.

[#15]

All,

I'm going to start using Power Query due to the amount of data I am using.

First, you are only seeing a portion of the data. Second, you are only seeing one of the many reports I am using from the data.

Lastly, with one year of data, my file is 20MB.

I did find a solution on Mr. Excel Message Board that I currently am using. While it is nice, it did seem to add a tremendous amount of overhead due to the calculations. In fact, I had to turn calculations off.

[#16]

@SeafoodDutch 

Fair enough. Just be aware that PowerPivot is designed to deal efficiently with large amount of data

[#17]

I'm uncertain about your comment.

A 20 MB file isn't a large file in your estimation?

[#18]

@SeafoodDutch 

Everything is relative :) One could consider 20 Mb as a large file, someone else as a regular file

My point was only to say that even if you have a large amount of data, Power Pivot could be an option