Counting Invoices when there are duplicates

Copper Contributor

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.Report with formula that's not working.

17 Replies
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?

@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.

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

@Patrick2788,

I just changed to restrictions.

Sorry about that.

@SeafoodDutch 

Thank you. I've attached a pivot solution.

@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?

Hi @SeafoodDutch 

 

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

@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?

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!
Not sure I undestand why you would need to use Power Query...

@SeafoodDutch 

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

 

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

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.

@SeafoodDutch 

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

I'm uncertain about your comment.

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

@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