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: 4310
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

Customer Question

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


 


Data attached

Attachment: 2013-07-04_011152_problem_.xlsx

Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year ago.
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., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4310
Experience: Computer Software Specialist for more than 10 years
Jess M. and 8 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.
Hi Jess,
I need a function - not a pivot if possible.
Expert:  Jess M. replied 1 year ago.
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 1 year 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

Expert:  Jess M. replied 1 year ago.
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 1 year 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.

Expert:  Jess M. replied 1 year ago.
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 1 year ago.

Jess - you're the best!


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


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

Expert:  Jess M. replied 1 year ago.
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 1 year 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!

Expert:  Jess M. replied 1 year ago.
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

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions