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

At work I frequently need to manipulate data from excel to

Customer Question

At work I frequently need to manipulate data from excel to produce charts and graphs. Currently from the list of data I make a side list of the post code and the number for all the postcodes which takes forever to do then highlight my list to create a
graph. Is there a way I can make excel summarise this information without me manually have to count and type it please?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Math-Stat replied 1 year ago.

Hi

Thank you for using this site.

Can you post a sample of actual excel data you are dealing with?

Thanks

Customer: replied 1 year ago.
Sadly not easily as I am on a home computer and it is a work computer where the data is held. It is essentially a list on exel with several tabs one of which is a poscode and I would like excel to be able to calculate each postcode value so that I can then graph it. At the moment I am manually counting them and listing them to the side of the table to create the graph
Expert:  Math-Stat replied 1 year ago.

ok

Customer: replied 1 year ago.
Is it something you can help with please?
Customer: replied 1 year ago.
I will see if I can get file example to attach....
Expert:  Math-Stat replied 1 year ago.

How about can you make a sample/mock excel file even with a few data, so that I will have an idea what can we do about it

Customer: replied 1 year ago.
In simple terms it is an excel spreadsheet with several tabs one of which is postcodes - I can filter the poscodes so they are grouped together in the list. What I then need is for excel to auto calculate how many from that list is cb2 for example and how many are cb3 and how many are cb4 etc....Does that help?
Expert:  Math-Stat replied 1 year ago.

hmm, have you tried using vlookup and other similar functions, then using if else to count it automatically?

Expert:  Math-Stat replied 1 year ago.

better send a mock excel, even with just 10 data so that we can treat it properly.

Customer: replied 1 year ago.
Apparently there is a function on excel that does do this my collegue just cannot recall where it is or how to do it....
Expert:  Math-Stat replied 1 year ago.

will wait for the mock excel data. Thanks

Customer: replied 1 year ago.
Please find attached as requested
Expert:  Math-Stat replied 1 year ago.

ok, on the first tab, what exactly do you want to do?

Customer: replied 1 year ago.
The postcode tab. I can filter it ok so that each postcode is listed together but then I need excel to calculate the number of each of the postcodes so that I can export it into a graph
Expert:  Math-Stat replied 1 year ago.

ok

Expert:  Math-Stat replied 1 year ago.

Here is how to filter:

To be able to filter the column "postcode" highlight the column to be filtered (column D) then click the "Data" tab between "Formulas" and "Review" tabs, then there you can see the "Filter" tab, just click it, and you can choose what among the postcodes you want to filter,

*************************************************************************************************************************************

If you are satisfied and happy with the answer, please choose a high rating. If you need more information, clarifications, or are not satisfied yet, choose 'Continue the conversation'. Bonuses are very much appreciated. Thanks!

Customer: replied 1 year ago.
I knew how to filter as I had mentioned already. What I need is to know how to make excel calculate the number of each of the different postcodes
Expert:  Math-Stat replied 1 year ago.

ok

Expert:  Math-Stat replied 1 year ago.

Have you tried using the "If -else" function to convert the required postcode to be counted to 1, and those which are not, to 0; then to count the total number of post code, just use the summation formula, have you tried this one?

Customer: replied 1 year ago.
Can you walk me through what that means please? I have filtered the postcodes and I can see the 'what if' icon. What do I do from there please?
Expert:  Math-Stat replied 1 year ago.

ok please hold on

Customer: replied 1 year ago.
Sure no problem, will wait to hear back from you
Expert:  Math-Stat replied 1 year ago.

Hi there!

Attached below is the link of the word file of the complete answers. Please copy and paste the link, and download.

https://www.mediafire.com/?2xq7ubx99zlulzb

Thanks

*************************************************************************************************************************************

If you are satisfied and happy with the answer, please choose a high rating. If you need more information, clarifications, or are not satisfied yet, choose 'Continue the conversation'. Bonuses are very much appreciated. Thanks!

Kindly remember the ONLY WAY experts receive any credit at all for spending time with customers is if you click on OK, GOOD or EXCELLENT SERVICE even though you have made a deposit or are a subscription customer. YOU MUST COMPLETE THE RATING FOR THE EXPERT TO RECEIVE ANY CREDIT. So kindly do not forget to rate and accept the answer, thanks!

*************************************************************************************************************************************

You can request me in future by writing "For Mr. Glenn only" at the start/title of your question.

Expert:  Math-Stat replied 1 year ago.

copy paste the postcode you want to be counted, into the cell that is colored red, and after that, it will be counted automatically and the total number of those with that post code will be shown at cell in column "I" under the word "Total" (the colored light brown cell) try and you will see it works

Thanks

*************************************************************************************************************************************

If you are satisfied and happy with the answer, please choose a high rating. If you need more information, clarifications, or are not satisfied yet, choose 'Continue the conversation'. Bonuses are very much appreciated. Thanks!

Customer: replied 1 year ago.
This isn't the way my collegue was able to do this - if I have to manually highlight each seperate postcode any way for it to add it up I amy as well just add them up myself as I had been. There is a function on excel that automatically calculates the numbers of each differential (in this case postcodes...) is it better to release the question to someone with indepth excel knowledge do you think?
Expert:  Math-Stat replied 1 year ago.

ok last try

Expert:  Math-Stat replied 1 year ago.

Have you tried the " =COUNTIF(D5:D999,"SG8 9BN")" excel function?

Customer: replied 1 year ago.
I don't even know what that is! I essentially need a list to export into a graph that says SG3 = 14, CB2 = 3, SG1 = 7 etc...
Expert:  Math-Stat replied 1 year ago.

I think what your friend is using is an excel where in he uses an "adds in" where one can tally automatically.

Customer: replied 1 year ago.
Well I don't know what that is and we all have standard issued laptops so they are all the same....
Expert:  Math-Stat replied 1 year ago.

click "File" then click "options" then click the "Add - Ins" there you need to enable and activate some features of excel for special functions.

Customer: replied 1 year ago.
what add in do I need??
Expert:  Math-Stat replied 1 year ago.

Here is what I have searched:
IN EXCEL

  1. Enter the date, party name, ledger name and the amount in the excel.
  2. Select the voucher type (multiple types are supported in single upload)
  3. After entering all the vouchers, Click on 'Create Vouchers XML' & save the Vouchers xml file in an easily accessible location
  4. OPTIONAL: If you want to export masters also, then click on 'Filter Masters'
  5. List of masters to be exported will be displayed. Select the master type and then click on 'Export Masters XML' & save the file in an easily accessible location

IN TALLY

  1. Import the Masters xml first, if you have exported it from excel.
  2. Import - Vouchers xml
  3. DONE!!!!

IMPORTANT NOTE: For successful importing of entries ensure

  • Masters (ledgers) already exist in tally before importing vouchers.xml
  • the date format in windows has to be in "dd-mm-yy" format (to do that Click Start, point to Settings, click Control Panel, and then double-click Regional Settings. On the Date tab, click the date format you want to use in the Short date style box, and then click OK.)

The Exported xml can be imported to Tally 7.2, 9 and ERP.

Customer: replied 1 year ago.
I think this reply was meant for someone else?? That makes no sense at all. I just need to know what add in to select so I can get the desired result...
Expert:  Math-Stat replied 1 year ago.

ok I will just opt out so that computer experts can help you

Expert:  John D replied 1 year ago.

Hi my name is***** and I am happy to help you with this issue. This is really a straight forward task and does not need all that complicated procedure as suggested by the other expert.

Customer: replied 1 year ago.
Ok great thank you!
Expert:  John D replied 1 year ago.

have a look at this image

Expert:  John D replied 1 year ago.

Here is the file with the formulas in the yellow cells

Expert:  John D replied 1 year ago.

If that helps please remember to rate my answer so I get credit for my work. Thanks

Expert:  John D replied 1 year ago.

And I am happy to answer any questions you may have

Customer: replied 1 year ago.
just a couple of questions please: the numerical values shown in the yellow section relate to the first part of the postcode only correct? Eg CM11Assuming the above is correct can I then export that from the yellow tab onto a graph?Lastly where do I put the code instruction so I access the same feature that you sent please?Thanks so much you've already been heaps more helpful in just 1 message!!!
Expert:  John D replied 1 year ago.

Yes your first statement is correct. The formulas count the number of occurrences of the FIRST PART of the code as requested by you in the sample that you gave. I can plot the results fro you but that would require additional steps to obtain a UNIQUE list first. Please go ahead and rate my answer and I will continue to help

Customer: replied 1 year ago.
Very happy to rate you highly.
That's great is is summarising the first part of the postcode only. Can I use that yellow column to export to make a graph?
Where / how do I implement that header code you sent me so that I can do these calculations on any speadsheet? Thanks.
Expert:  John D replied 1 year ago.

Ok I am preparing an explanation although these are additional procedures that were not really part of the original question which I believe I have given you the solution for. So the rating should not really be held

Expert:  Math-Stat replied 1 year ago.

Hi

I am sorry to interfere, but I have noticed, the entries in the yellow column are wrong, for instance, the total number of CB10 1QB when I used the filter option of excel, is just 3, and not 23, as what is shown in the yellow column, kindly check again.

Expert:  John D replied 1 year ago.

Glenn I don't think you really understand what these formulas do. You also seems to have missed the subsequent messages which explain the issue. I suggest you contact me directly if you need more clarification as I don't think this is the proper place for us to experts to have our discussion

Customer: replied 1 year ago.
Once I rate though this conversation ends so sort of defeats the point. You've given me a header formula but with no explaination of how to use it or where to insert it which is a follow up question based on your answer. Also my original question stated that I needed this function to export the data on graphs so is reasonable to check that what you have suggested can do that.....thanks.
Expert:  John D replied 1 year ago.

Not true. The question will remain open after rating and I WILL be sending you the requested details

Customer: replied 1 year ago.
I have never been asked to rate an expert prior to the issue being completed. Is there a reason you are insisting on this?
Expert:  John D replied 1 year ago.

yes because I am skeptical of clients taking the solution and walking away without paying. It has been happening a lot lately. I have given you the solution in good faith and I will continue helping, but you also need to show some good faith

Customer: replied 1 year ago.
I am on a membership basis so you automatically get paid anyway as long as I rate you 3 stars or higher. In fairness you haven't fully completed the issue I raised either. I am happy to rate you highly once the job is compete - otherwise please release the issue for another expert to respond to.
Expert:  John D replied 1 year ago.

what exactly do you need to know that was CLEARLY PART OF THE ORIGINAL REQUIREMENT

Expert:  John D replied 1 year ago.

this was your original question: . Is there a way I can make excel summarise this information without me manually have to count and type it please?

Expert:  John D replied 1 year ago.

and I have answered it in full and also offered to extend my help. But that is no reason for you to hold the rating beyond the delivery of the original solution

Expert:  John D replied 1 year ago.

just so you know NO I DO NOT AUTOMATICALLY GET PAID as you mentioned. Are we going to waste more time on what this question is worth

Expert:  John D replied 1 year ago.

so let's go on with this and bring it to a close

Customer: replied 1 year ago.
I do not have an answer to my question, I have no idea what that code you sent refers to or where it needs to go to make it work for me and as Glenn mentioned the sums do not add up. Please release the question to another expert as I now feel this is becoming a rather ubrupt and borderline rude exchange - please do this with immediate effect to prevent me making a formal complaint.I have always had wonderful service on here and this has really tainted the whole thing. Professionalism at all times has to be a must and frankly you've just been rude and I still don't have a solution.
Expert:  John D replied 1 year ago.

will do after you undertake to destroy the file and remove it from your devices and also not use any part of it or allow other experts to use it in their solution. I consider it copyrighted material since you have not paid for my work

Customer: replied 1 year ago.
Agreed. Please release my question to an alternative 'professional'
Expert:  John D replied 1 year ago.

you need to literally make that statement for legal reasons

Customer: replied 1 year ago.
Either release my question or I will make a formal complaint, the audit trail above is very clear on what I have agreed to do.
Expert:  John D replied 1 year ago.

Be my guest. One of the reason this site has become a failed endeavor among its piers is because it allows customers like you to enslave their experts in the manner you have done. Mind you the sites takes 50% cut of what you pay as compared to other more reputible sites' which take 5% to 10% only

Expert:  John D replied 1 year ago.

You can also go ahead and rate me with 1 star since you are that kind of cutomer

Expert:  John D replied 1 year ago.

*customer

Customer: replied 1 year ago.
This is now my 3rd request to release this question to another expert otherwise I will take further action. I find the way you communicate with me insulting and no longer wish to have contact from you. Any further replies from you will be considered harrassment. Please release my question as requested now 3 times.
Expert:  John D replied 1 year ago.

Really!, It is customer like you are the ones harassing the experts. You have not yet confirmed that you have indeed removed the file from your devices. Until you do so or pay for the work I will seek other venues to get compensated for 1) the solution, 2) the time spent on these ridiculous conversation, and 3) any legal costs related to this pursuit

Customer: replied 1 year ago.
I have confirmed as can be clearly demonstrated above.I won't be replying to any further messages from you and will commence a complaint down all formal routes available both within the justanswer head office and external organisations.
Expert:  John D replied 1 year ago.

You will not be able to reach me at this site any longer as I am cancelling my subscription. So for any future communication (legal or otherwisw) here is the address you can reach me at XXXXXXXX

Customer: replied 1 year ago.
Release my question please so another expert can respond (4th request)
Expert:  The-PC-Guy replied 1 year ago.

wow, I can see your entire history on this question, and I am sorry you have not found a satisfactory solution yet.

I believe I can help you

have you tried a pivot table, which is the easiest way to get a count of unique items.

Then from that data you could create something called a pivot chart.

NOTE: that as with any chart in excel that if you are plotting a lot of data points, that some of the label names will not show up on the chart axis. This can be corrected by plotting fewer points on a given chart.

here is an example.

http://wikisend.com/download/777878/JA-Example-JA-Mod.xlsx

I would be happy to walk you through exactly how that was done by remotely connecting to your computer and showing you the steps. There will be a small additional fee associated with that.

if you are happy with my service then I would ask you to give me a 3 star or higher rating.

Let me know if you need something further on this matter.

Expert:  The-PC-Guy replied 1 year ago.

did you see my previous response

Expert:  The-PC-Guy replied 1 year ago.

you asked for another expert and I am standing by to help.

Related Microsoft Office Questions