I run an online golf tour and I need a little help with the coding of my handicap excel sheet. (For any golfers out there this is a custom handicap system, not the R&A version).
First I'll explain what I have, then my problem.
I have from left to right the following, Name, Scores, Worst Score, 2nd Worst Score, Average, Hcp. (There are other columns but are not important here)
The Scores columns record each round played, so at the moment we only have 10 columns with data as its the start of the season. I currently find the "Worst Scores" from the 10 columns, as they are all relevant at the moment. My problem will arise when I get over 12 columns of data. From that point on I need to find the "HIGHEST 10" from the "LAST 12" entries.
Easy you might say, BUT the last 12 rounds played could be spread over 100+ columns and every row could be different.
Example, Player 1 could have scores in column A to AB and then in AD to AE. Where as Player 2 could have scores in every alternate column.
I'm assuming I'll have to set up some form of count to count the cells that have data in them until I get to 12. Then extract the ones I dont need.
My question is, How do I find the last 12 column cells used in any row AND extract the highest 2 from those 12?
I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number
What I did however in the after image, as can be seen, is I took the data that appeared on the repeating row (Row 3 from the Before image), and included the PRICE, COST, BEGIN, and END values in new columns on row 2.
The model number is the same of course, which is why I did it that way.
If the model numbers were different, I would have simply left it alone, as-is.
Is there a way to accomplish this via some functions or maybe even a VBA script?
By the way, if we have to dump all of this onto a new sheet, then so be it.
Column A contains names Column B their unique ID number Column C the date a transaction occurred
How can I count the number of entries between two dates i.e. 1-31 April regardless of duplication or anything? I've tried a few things mentioned in other threads but none seem to work.
My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).
How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?
I have two colums. In A1:A4 are ticket numbers of products made. In B1:B4 are the number of stillages that the tickets relate to. What i am trying to work out, is a formula that will calculate the number of stillages produced based on the fact that each ticket number begins with "150". I have tried using COUNTIF, MATCH and FIND functions, but when i does work, will only return a value of 1.
I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.
I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))
Im trying to make an excel spreadsheet for work for working out how many of each product and individual staff member has sold
I have 2 columns one with staff and one with what they sold e.g.
Staff Member - Sales Staff 1 - prod A Staff 2 - prod B Staff 3 - prod A Staff 2 - prod C Staff 1 - prod A
and have another table, the one that i want to populate, that looks like this
Staff 1 prod A = prod B = prod C =
Staff 2 prod A = prod B = prod C =
etc
I cant seem to find a function that will work out how many of prod A staff 1 has sold and how many prod B he has sold etc
i have tried things like countif but i cant seem to get them to link the staff member and sales boxes together, the only way i can think of is to write down an equation for each line, but i have hundreds of sales and that would takes ages
I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.
I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.
I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.
I have two worksheets, 'monthly budget' and 'summary sheet'.
Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-
I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.
Every other line is the planned production and the others are filled in after as the actual production I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.
Here is my problem- if a station is running two different products it gets counted twice. I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines. Attached is an example of what I have with the formula and below that is what I am looking for
I'm trying to create a worksheet with only one unprotected cell (simple enough!) and I want to base calculations in other protected cells on a running total of how many times a value has been entered in my unprotected cell. Re-entering the same value would need to be counted.
First enhancement would then be a means of resetting the counter.
Second enhancement would be to check for a valid entry and only then increment the counter.
I am trying to insert a formula that counts the number of dates within a certain date range. why this sample file does not work, the answer I am looking for in cell C1 is 3.
I'm trying to craft a gradesheet for my son's teacher and I am runnning into a problem obtaining a students average. In one row I've listed the possible questions for all the tests whether the test has been taken or not. In the row below I've listed "questions answered correctly" for only the tests they've taken. I need a way to calculate the overall avergae of the student while ingoring columns with null values in the "questions answered correctly" row.
I know there is a way to ignore zeroes by excluding them with an IF statement but I didn't want to do that in case one of the students actually did get a zero. As the attached example shows the simple sum function renders a 25% average while the average should actually be 50%.
I have a relatively large data set that is separated into columns A-G. In column G, I have a multitude of values that are separated by commas. What I would like to do is to separate these entries into rows while keeping those values in A-F constant.
Here is an example of what I would like to do through VBA:
ex1.jpg
to
ex2.jpg
Also, kudos. This website has now gotten me interested in VBA.
I copy rows of data according the Workgroup entries in Column C and paste them in to the relevant team sheet so that it is separated and printed off. Currently I am doing this manually, as the report length is variable and can be a few hundred rows or sometimes a few thousand which takes up some time. I know I can use the auto filter option, but as I currently use a macro to format this report I was hoping to include it whilst it is being formatted. Also would like to apply all borders to report so its easier to read when printed and a comments column at the end, but this can be done later.
There are teams and 27 workgroups to separate into each team, I have to do this everyday and was hoping to save some time here. I've attached a sample work book as an example.
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.
All the cust numbers are unique so would I be able to use a wildcard?
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such... A, B, C, D, UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
I am extracting data from an electronic medical record (EMR) which has many search limitations. I am able to export data into a CSV file (attached) to perform some calculations (All the patient data is fake btw). I am trying to find the number of appointments for each patient within a specific date parameter. For each patient, one appointment appears on one row.
pt appt sample.png
What I want to do is count the number of rows (appointments) for each patient in an efficient manner. Right now I've just used the COUNTA function, but don't want to repeat this for every patient, due to the way that the workbook is set up. The data is all from the CSV file from the EMR; the only column I've added is the "Appointments since 2013" column.
The patient data is fake because I am using a test server for the EMR. However out in a real doctor's office, there will be over 2000 patients with possibly up to about 100+ appointments.
Not sure if there's formula out there that would make my task a lot easier and more efficient, or I need to play around with the columns to set it up differently.
