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, Computer Manager
Category: Microsoft Office
Satisfied Customers: 2011
Experience:  20 years experience providing remote computer support
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: 5 years ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 5 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 5 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



Expert:  The-PC-Guy replied 5 years ago.
ok, you can upload to 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 5 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 5 years ago.
actually I am going to sleep now, but I promise I will look at it first thing in the morning
Customer: replied 5 years ago.


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 5 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 5 years ago.
After 6 hours I have done it see the link

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 5 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 5 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 5 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 5 years ago.
If I am required to pay a list of formulas would be helpful otherwise I am done.
Expert:  The-PC-Guy replied 5 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
Customer: replied 5 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 5 years ago.
lemme try again
Customer: replied 5 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 5 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 5 years ago.
here is the updated one. I fixed the formulas, hopefully this will help you to do this much easier in the future.

Is this what you were looking for? if it is please ACCEPT, if not I'll try again
Customer: replied 5 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 5 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 5 years ago.
Maybe if I send you my final spreadsheet that I put together that might help
as you can see I what to analyse the combination of clearance code categories for duplicate faults.
Expert:  The-PC-Guy replied 5 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 5 years ago.
I appreciate your help and I appreciate your prompt responses
Expert:  The-PC-Guy replied 5 years ago.
no problem
Expert:  The-PC-Guy replied 5 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 and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 5 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 5 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 5 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 5 years ago.
ok thankyou, let me know if you need anything else in the future