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 The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1307
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Ok here we go I need your assistance with this one as I am

Resolved Question:

Ok here we go I need your assistance with this one as I am sure you can sort it out quicker than I. To put some context around this I have data relating to Field technician Assurance Service Calls and how the service calls were booked off. They are booked off with a clearance code which I have categorised into 7 categories (CAN, CPENFF,EXCHANGE, FOHTESTER,Network,NIA,NFFFOH) so that helps. The large spreadsheet with 20,770 entries contains a months service call data. It has multiple columns but I need to work out some figures involving two of the columns. The first of those columns contains the Customers service number. So my focus is on when those service numbers with duplicates appearance during the month. So I can simply sort on that column and do some duplicate stuff and found 2377 duplicates which make up 5197 of the entries. But the real analysis comes in finding out what combination of clearance code categories they have. With the added twist that it does make a difference which was the first service call (the data does have columns with dates in it). So a CAN then a CPENFF is different to a CPENFF then a CAN. Off course CAN then CAN is fine. Ideally I would like the results simply to be in a table which shows of the 2377 duplicates CAN-CAN 133 CAN-NFFFOH 197 CAN-Exchange 171 etc etc

If required I can send you a cut down version of the sheet
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 2 years ago.
My Name isXXXXX will be helping you toady. If I provide good service, you may ask for The-PC-Guy in your question title for your future needs. I could try.

Before you send the file though, try playing with the countif function. Also try the sort or filter functions. If you still can't get it Ill take a look at a sample file you can send.

You will have to be a little more specific of the output you are looking for though.
Customer: replied 2 years ago.

I think I will send the file through and that way it will all make sense. How do I attach the Excel sheet?

 

The output will make sense once you see the sheet

 

Thank You

 

Gordon

Expert:  The-PC-Guy replied 2 years ago.
ok, you can upload to www.mediafire.com and when they give you a link, patse the link here:

But you will need to be a little more descriptive of what exactly you are looking to filter for. The sort and filter options work really nicely.
Customer: replied 2 years ago.
Just going to pick my daughter up from school will upload in about 40 minutes for you. Can I will describe exactly what I want
Expert:  The-PC-Guy replied 2 years ago.
actually I am going to sleep now, but I promise I will look at it first thing in the morning
Customer: replied 2 years ago.

 

http://www.mediafire.com/?4ylf3lh8jky67y1

 

OK column B I have sorted so you will see duplicates apperaing on consecutive rows like 10-11 & 28-29. If we take 10-11 this duplicate instance would be a CAN-CPENFF whereas 28-29 NIA-CAN. In some ways the best method is probably remove all FNN's (column B) that are not duplicates and then reduce each duplicate to a single row and have the additional column F added with the dupicate clear code category or if there were 3 entries column G would be utilised. I guess Conditional Sum or something could be used. What I need in the end is a breakdown of the amount of CAN-CAN CAN-CPENFF etc See how you go I will attempt something but I think what you come up with will be better and less manual than my approach.

Expert:  The-PC-Guy replied 2 years ago.
so all you want is a count of non duplicate text strings of each type in column b, "so if threre are 42 non duplicate cancans and 86 non duplicate cancpe? That is all you wan't to know?
Customer: replied 2 years ago.
After 6 hours I have done it see the link

http://www.mediafire.com/?atsmaa76vi53w2a

What I did was sort the doubles from triples and put them on separate sheets. Then I added the extra columns just picking up the next rows Clearcode and category (only really needed the category). Then copy and pasted values then removed the duplicates then copied the category into additional columns then merged them and pivot tabled them to get my results. You might have faster way with some whiz bang formula.
Expert:  The-PC-Guy replied 2 years ago.
well as long as you are all set, that is what is important.

I would have just used the countif function

=COUNTIF(A5:A3000,"can-can") or whatever range you wanted to use, this would give you a count of how many times that text occurred in that range
Customer: replied 2 years ago.

It was a two part problem with much work manipulating the data to arrive at the point of where CAN-CAN was possible. In many ways all the work is in getting the data to that point afterwhich it is easy

Expert:  The-PC-Guy replied 2 years ago.
thats a lot of the problem, but for the future, the filter options along with certain formulas make data manipulation quite a bit easier. Just my thoughts, use what you will. Was there still something else you needed me to do?
Customer: replied 2 years ago.
If I am required to pay a list of formulas would be helpful otherwise I am done.
Expert:  The-PC-Guy replied 2 years ago.
well I am not exactly sure how the payment works, you would have to take that up with customer service, as far as I know you have to make a deposit to list a question and Accept the answer for the expert to be paid anything.

The only thing I can give you since you already figured out what you needed was the countif formula i gave you earlier which is a fast way to count how many times something occurs in a excel file.

Also you may want to check out the filter and sort functions.

they can be found under the data tab in excel, gives you a quick way to get rid of duplicates, and only display text matching certain criteria.

I am not sure if this is what you were looking for

http://www.mediafire.com/?b87aftgkd2j1s6c
Customer: replied 2 years ago.

Not quite as your column F is only reporting if there is an exact match CAN-CAN, NIA-NIA. I was looking at picking up whatever the clearcode categories where for the subsequent faults. So if there was a duplicate it would pull the clearcode category for the second job regardless of what that category was and likewise if it was a triple it would pull the 2nd and 3rd categories regardless of what they are.

 

I think this should make things clearer

Expert:  The-PC-Guy replied 2 years ago.
lemme try again
Customer: replied 2 years ago.

Cool no hurry at all I meet my deadline but some smart formulas for next time will be good

 

Expert:  The-PC-Guy replied 2 years ago.
So just to clarify. If there is a duplicate or triplicate, only the 2nd value or 2nd and 3rd values get pulled out. Not all? If this is the case I may have just had my formula backwards, becasue I was pulling the first and 2nd.
Expert:  The-PC-Guy replied 2 years ago.
here is the updated one. I fixed the formulas, hopefully this will help you to do this much easier in the future.

http://www.mediafire.com/?d4rb066zz3zzt6a

Is this what you were looking for? if it is please ACCEPT, if not I'll try again
Customer: replied 2 years ago.

I am really sorry about this and appreciate your help but is this ccidentally the same spreadsheet with the old formula's

Expert:  The-PC-Guy replied 2 years ago.
yes, i changed the formulas a bit, so it would pick out the 2nd and 3rd ones instead of the first and second ones.

Did I misunderstand what you were looking for?
Customer: replied 2 years ago.
Maybe if I send you my final spreadsheet that I put together that might help
http://www.mediafire.com/?atsmaa76vi53w2a
as you can see I what to analyse the combination of clearance code categories for duplicate faults.
Expert:  The-PC-Guy replied 2 years ago.
ok, I won't be able to take a look tonite, but I will check your example tomarrow. Then I should be able to update the formulas accordingly.
Customer: replied 2 years ago.
I appreciate your help and I appreciate your prompt responses
Expert:  The-PC-Guy replied 2 years ago.
no problem
Expert:  The-PC-Guy replied 2 years ago.
OK, I looked at you example, and now I am really confused. I gave it a shot, but I don't think I can continue As none of this really makes any sense to me.
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1307
Experience: 20 years experience providing remote computer support
The-PC-Guy and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.
So what happens now. Do I just accept the answer as you have tried but not resolved it.
Expert:  The-PC-Guy replied 2 years ago.
its entirely up to you!


Maybe you can use the formulas I have done as a starting point. It is just way to complex for me to get it exactly like you want it
Customer: replied 2 years ago.
It was hardwork but I worked it out myself but you earnt your money even though you did not resolve the matter.
Expert:  The-PC-Guy replied 2 years ago.
ok thankyou, let me know if you need anything else in the future

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