Counting Macro Based On 2 Criteria (Horse-racing Spreadsheet)
Jun 21, 2006
Horses have 5 running styles; early speed to late closers; denoted as 1 to 5 in column Z. I'd like to count those noted as 1 thru 3 for each race.
In column FC (the last column with data) I have a race ID # for each race on the spreadsheet, somewhere between 2500 to 5000 races in each one. They're a monthly record. The race ID # is a concatenation of columns B (Track ID), C ( Date) & D (race #). Each is unique to its race. E.G. Aqueduct, jan 1st, 2004, 2nd race is id'd as AQU379872.
Would it be possible to create a macro that would (A) count the number of horse 1s, horse 2s & horse 3s from a specific race ID & then post those numbers in columns FD thru FF & (B) then continue to the next race ID in column FC & count those horses from that race & so on thru the entire spreadsheet?
Would it be simpler if each race ID were changed to a number--race 1 down to race 2500?
View 9 Replies
ADVERTISEMENT
Aug 3, 2007
I've made my own spreadsheet to record my racing bets. Everything works great, however I want to expand it to become more complex.
There are 2 formulas I'm having trouble creating:
1...the formula for dead-heat finishes including the Rule 4 deductions (2,3 & 4 way dead-hets)
2...the formula for each-way bets including the Rule 4 deductions (the win part & the placed part)
I guess only people with knowledge of horse racing / betting will be able to help me on this.
View 9 Replies
View Related
May 6, 2009
i need to do the following on the attached spreadsheet using a macro: SHEET 1. This is an example of the original data i will be working with i need
1) You will notice that the Min and Max columns are not all together, i need these to all be together. I then only want the Race course, race time, date and type, Min, Max and ratings.
2) I then want the following formula added to Column F, Max minus Min. In column G i would like the following formula Rating minus Min. And finally in Column H Rating minus Max.
3) I would the like to Highlight in Yellow the horse with the highest possible number. I want this done for each column (F,G,H) in each race i would like all Horses that dont have a yellow cell in any of the Columns (F,G,H) to be deleted, leaving me with just the highlighted horses. An example of the required final result is contained in Sheet 2 of the attached spreadsheet.
View 2 Replies
View Related
Oct 7, 2006
I have odds line numbers on this excel sheet which is the VL column. I'd like to be able to scratch a horse, (delete a horse from the sheet) and the odds line (VL column) will update to the new value after the horses is scratched from this sheet.
Now I do this in a program that uses VBA and transports this info to Access, where then I export to Excel to this sheet.
Here's the formula I use for the odds line...
#EQUAL(#COLVAL(51),1, .00001)*((#COLVAL(53))-(#COLVAL(53)*.4))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),2, .00001)*((#COLVAL(53))-(#COLVAL(53)*.25))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),3, .00001)*((#COLVAL(53))-(#COLVAL(53)*.10))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),4, .00001)*((#COLVAL(53))+(#COLVAL(53)*.05))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),5, .00001)*((#COLVAL(53))+(#COLVAL(53)*.10))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),6, .00001)*((#COLVAL(53))+(#COLVAL(53)*.25))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),7, .00001)*((#COLVAL(53))+(#COLVAL(53)*.40))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),8, .00001)*((#COLVAL(53))+(#COLVAL(53)))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),9, .00001)*((#COLVAL(53))+(#COLVAL(53)*1.3))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),10, .00001)*((#COLVAL(53))+(#COLVAL(53)*1.6))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),11, .00001)*((#COLVAL(53))+(#COLVAL(53)*1.9))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),12, .00001)*((#COLVAL(53))+(#COLVAL(53)*2.2))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),13, .00001)*((#COLVAL(53))+(#COLVAL(53)*2.7))/(#COLVAL(50))
I added a couple of columns in the excel sheet to occomadate this formula which should work if coded into excel properly. Notice the (50), (51), (53) numbers.
(50) = TC in this excel sheet
(51) = 2V in this excel sheet
(53) = TCa in this excel sheet
VL column is where this formula updates to.
I have one other minor problem with something else, but so not to confuse, I'll mention it later.
View 9 Replies
View Related
Sep 28, 2009
I have a line graph which I use in conjunction with live horseracing odds. On the vertical axis are the odds which are sourced from a worksheet. Each horse is represented by a data series that moves along the horizontal axis. The refresh rate is determined by the user embedded in the code.
I would like to expand the length of each data change along the horizontal axis as the data can be volatile. Sometimes there are rapid changes on the vertical axis which makes it difficult to interpret. With each refresh the line data moves about 3mm. It would be much better if it were about 5mm, especially as I now use a widescreen display.
There are no values on the horizontal axis as such, only time is suppose set by Excel?
View 9 Replies
View Related
Oct 28, 2008
I've got two worksheets. One contains data from a query, the other worksheet should count rows in the queryresults based on two criteria. See example.
I tried doing it with sumproduct in the normal variant and the array-variant...
View 8 Replies
View Related
Aug 29, 2013
I have data in my worksheet as follows
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
[Code]....
I want to create a sum of all values in column B (Data Size) that correspond to the same Dept-div code in column A
Ex: 0667 the total should equal 43268.8 for the two cells.
I need to count all cells in these columns versus a specific range?
View 2 Replies
View Related
Aug 15, 2007
Need to writing a formula that will calculate the number of times a contract is used each month. Problem is that RMA numbers sometimes repeat. So I need to count the contract number for every unique RMA in a month......
View 5 Replies
View Related
Aug 11, 2008
I have a spreadsheet that has Leads in column H for eg Advertisements and Presentation dates in column K
I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored
View 9 Replies
View Related
Jul 23, 2009
I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.
View 4 Replies
View Related
Aug 11, 2007
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
View 4 Replies
View Related
Aug 17, 2006
Need a way to count the number of times a certain bit of text occurs in a range based on a given criteria? Say I've got a column of names and another column with true or false in it (next to each name), how could a do a total of how many times 'TRUE' occurs next to each name?
View 3 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
May 12, 2006
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
View 3 Replies
View Related
Oct 30, 2013
I am using Excel 2010.
BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500
MainList - F2= (current month)
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
View 2 Replies
View Related
Jul 19, 2006
I have to look through an entire table and find certain criteria (eg. "STEVE'S PIZZA SHOP"). It might be in columns G or H, or it could also be in Columns C or something of that nature. What I'd like to do is search through the table, find each instance of said criteria and cut each row the criteria apperas in to a different sheet. I'd like to do this as a macro, so I can set it up for other criteria as well. In addition, if I can include in the macro a way to create a header row (which I'm pretty confident I can myself), as well as change the title of the sheet the information is moved to. I have looked up Do-While loops, For-Next loops, If-Then loops. I am at a total loss.
View 9 Replies
View Related
Jul 17, 2014
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company
-------------------------------------------
1/1/11 Jane A Company
1/1/11 Jane B Company
3/1/11 Bob B Company
2/1/11 Bob B Company
4/1/11 Bob B Company
5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
View 1 Replies
View Related
Jul 5, 2014
I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.
How do I count the number of candidates in the spreadsheet?
In Cells A6:B5, I have "Submitted"
In Cells A7-?? I have dates for those candidates that have been submitted.
Then a new section titled, "In Process"
A list of candidates are contained within that section.
Then a new section titled, "For Review"
etc.
There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.
I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.
(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)
View 4 Replies
View Related
Oct 8, 2007
I'm making a spreadsheet to record the results of a horse sale, and I'm trying to make a summary sheet to list various statistics such as the 10 top sellers, 10 worst sellers, etc.
Now, I know I can get the 10 top prices easily by using Large(<Range>, 1...10) in my cells but I can't figure out a way to properly determine the hip # and name. Everything works fine when there's only 1 entry at price n, but when there are multiple I run into problems.
EX:
Horse 1 sold for $5500
Horse 2 sold for $8500
Horse 3 sold for $5500
Horse 4 sold for $6000
So, my summary should show
Horse 2 $8500
Horse 4 $6000
Horse 1 $5500
Horse 3 $5500
View 9 Replies
View Related
Jul 21, 2012
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
View 9 Replies
View Related
Nov 7, 2007
First off, can someone guide me to a better formula to E2:E14 or E21 if used later without the array? Other folks will use this workbook. Next, is there a way to do that without using columns F2 or G2. There are numbers below 0 to take into account that need ranked. The whole idea is to rank them as to whatever #'s are closest to 4.7000. I eventually want it to match the name in column- A2 to the appropriate ranking. 0.0000 is #1 and the highest abs would be #13-(1.0098) in the sheet. If anything in A2:A21 is added or removed I'd like the entire row to remain "blank".
************************************************************************>Microsoft Excel - newstart.xls___Running: xl97 : OS = Windows ME (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD2E2F2G2H2D3E3F3G3H3D4E4F4G4H4D5E5F5G5H5D6E6F6G6H6D7E7F7G7H7D8E8F8G8H8D9E9F9G9H9D10E10F10G10H10D11E11F11G11H11D12E12F12G12H12D13E13F13G13H13D14E14F14G14H14=
ABCDEFGHI1Name / Car #1st Pass2nd PassBest Run4.70000 Name2Bartlett 5254.65694.55594.6569-0.04310.00000.00001Moody3R. Stringer 41334.72264.58994.5899-0.11010.00010.00012S. Stringer4Norman 4894.73474.68924.6892-0.0108-0.00030.00033Powell5M. Jones 46594.73534.69934.6993-0.00070.00040.00044A. Mears6Powell 4134.73944.69974.6997-0.00030.00050.00055K. Rierson7Penner 42914.78304.99034.78300.0830-0.00070.00076M. Jones8Moody 49074.81024.70004.70000.0000-0.01080.01087Norman9A. Mears F4094.92884.70044.70040.00040.01180.01188D. David10K. Rierson 450R5.57334.70054.70050.0005-0.04310.04319Bartlett11Muse 47995.84025.70215.70211.00210.08300.083010Penner12LH Newlin 44425.84525.70985.70981.0098-0.11010.110111R. Stringer13D. David 48386.42984.71184.71180.01181.00211.002112M. Muse14S. Stringer 4229.20154.70014.70010.00011.00981.009813LH Newlin15 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
View 12 Replies
View Related
Dec 30, 2008
I have a spreadsheet of about many rows and 5 cols shown in the attached. I show 3 races..each will have a different time to the previous race. I want to use the rsq function to compare the data for that race in col c with that in col d and produce the answer for the 1st race in each row in col e for that race..then proceed to do the same for race 2,then race 3 etc...
I need a formula in e2 I can drag down...each cell in col e should be filled with the answer for that race..I have thousands of races..
View 4 Replies
View Related
Sep 29, 2009
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):-
=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))
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.
View 5 Replies
View Related
Aug 17, 2012
I have a data which i need to total based on document types.
Document types in cells are AB=CELL A1, ZR=CELLL A2, ZP=CELL A3,
SK= CELL A4.And amounts in rows 555=CELL A1, 625 = CELL A2,
725= CELL A3, 925=CELL A4 and so on.
My challenge is to get all the total of ZR,SK,AB type of documents using macro.Initially i used this formula, however now document types are more than one.
=SUMIF([Book1]Sheet1!$C$2:$D$4,"zr",[Book1]Sheet1!$D$2:$D$4)
View 6 Replies
View Related
Sep 11, 2006
I have ahseet with the following columns:
Col 1 = Item Description
Col2 = Price
Col3 = Discount Rate
I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
View 5 Replies
View Related
Oct 22, 2013
Attached is my .xls with dummy data Fetch data By Director.xlsx.
My intention is that once the Director and Month in tab 'By Director & Month' is chosen and the Fetch Data commandbutton is clicked, a new workbook should open.
This new work book should have :
all entries in tab 'Monthly Invoice Input' for that Director-Month combination,along with a sum total of column Q(Total invoice amount) in the last row.
View 6 Replies
View Related
Jan 22, 2009
I have a table of data with which I need a macro to clear a range of data based on certain criteria. I have attached a file as an example. In this example the current period is 6. I therefore need to clear columns which have a period of 7 or higher.
Essentially, if the current period is x, then clear the range in the period columns if the period is greater than x.
View 6 Replies
View Related
Jan 29, 2009
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
View 2 Replies
View Related
Feb 20, 2009
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
View 3 Replies
View Related
May 16, 2011
I have a file, with this dummy data
Fruit Country QTy
Apple India 100
Apple USA 200
Orange India 150
Grapes Aus 100
Grapes India 100
I need to macro, wherby it will create 3 files namely apple, orange,grapes and store the relevant rows.
Hence I need a macro to split file into different files based on different values in a column and accordingly name it .
View 9 Replies
View Related
