How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6393
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

Calculate Unique Data in front of each account

This answer was rated:

 I need a function to calculate all the 'unique' dates infront of each account. Thanks!


 


Data attached

Attachment: 2013-07-04_011152_problem_.xlsx

Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

The easiest way to do this is by using Pivot Tables. Please download this modified file showing the number of days or dates that the store placed an order:

http://wikisend.com/download/737764/2013-07-04_010937_problem_REV1.xlsx

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M. and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.
Hi Jess,
I need a function - not a pivot if possible.
Messi,

In the data that you provided, stores are duplicates and dates are duplicates. The pivot table is a better presentation of this data because it will list the store once and the dates in front are counted.

How do you intend to display this "count" of duplicate dates? Is it opposite the duplicate listing of stores?

Jess


Customer: replied 3 years ago.

Jess -


 


I looked at the file in detail now - but it's not solving the problem.


The pivot table is only showing the 'count' of dates.


 


I'm looking for the 'count' of unique dates.


 


So for example 10th avenue gourmet should only show '1' because there's only one date - which 6/17/2013

Hi Messi,

Thank you fr the clarification. So this means that you only need to count unique dates once like the example you mentioned. In this case, we just need the pivot table to summarize the STORES and the DATES and then leave the counting of unique dates to the IF and COUNTA functions.

Here is the completed Excel file:
http://wikisend.com/download/150608/2013-07-04_010937_problem_REV2.xlsx

So in this case, the pivot table is only used to give a summary of the stores and dates to remove duplicates. From there, I use the COUNTA function to count dates in the rows.

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

Jess,


 


Thanks a lot of all the help again.


We're on the right track - but still haven't got the answer I'm looking for.


 


For example, I want the answer to be in the PIVOT or as a function in the pivot source - so it can be dynamic - and it will update automatically everytime I refresh the data, or change the sort of the pivot.


 


Also - It's still not showing me the actual the number of dates per store as a total.


 


Ideally, I'd like one column to display the total number of unique dates.


 


For example:


10th ave gourmet 1


Astor Row cafe 2


Fairway Douglaston 4


 


Thanks again.

Messi,

I got a solution for your problem. Initially, you wanted a formula or function to count unique dates in your data. In the sample that you gave me, I added a third column to do exactly just that. It will count unuqie dates per store. In the count, you have the option to display 0 to duplicate dates or to display a blank.

Now, if you want to have a perfect list summarizing the stores (since in the original listing stores are duplicates also), you still need to use Pivot Table. I also made the Pivot Table for you.

The logic here is to add a third column to count unique dates per store, then make a pivot table based on the store against the SUM of the unique dates, not the count of the dates.

Here is the download link:
http://wikisend.com/download/233128/2013-07-04_010937_problem_REV3.xlsx

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

Jess - you're the best!


I've been trying to solve this problem for daaaaays!


That's exactly what I was looking for!!!

Messi,

Thank you for writing back. I am glad that you were satisfied with my answer/service. I am also glad to be of help.

If you need assistance in the future, you can request me or post a question "For jessmagz" so that I can get back to you immediately.

Best regards,
Jess
Customer: replied 3 years ago.

Thanks Jess,


 


No doubt I'll have many more questions to come as I build this spreadsheet.


 


Anychance you can explain the logic behind the =IFSUMPRODUCT function you put in?


 


Thanks!

Messi,

Thank you for writing back. The outer function is basically the IF function. It simply tests if the stores are duplicates and the dates are duplicates. That is the > 1 part there, the part of the logical test if the IF function.

So if it is greater than 1, the line shall display blank, if not, display 1. This is the reason why only the first unique entry is counted as 1 since it is the first occurrence where the logical test for ">1" is false.

Now, regarding the testing for the uniqueness of the stores and the dates, it is evaluated by the SUMPRODUCT function. It is an array function. More information about this function can be found in this link:

http://office.microsoft.com/en-001/excel-help/sumproduct-function-HP010062466.aspx

Please remember to rate my service positively (3-5 stars/faces) if this helped.

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess

Related Microsoft Office Questions