Match Names To Fastest Times
Feb 6, 2010
I thought i had worked it out, but for some reason the names aren't updating when i add a new time, and the same names appear when i add the same code for a different track. I was using this code {=INDEX(A2:A32,SMALL(IF(C2:C32<>0,ROW(C2:C32)),1),1)} as you can seen in cell A34, then added a 2 onto it for the second fastest time eg.....{=INDEX(A2:A32,SMALL(IF(C2:C32<>0,ROW(C2:C32)),2),1)}, but doesn't seem to update when adding a new fastest time in. Am i using the wrong code to match the names with the fastest times?
View 4 Replies
ADVERTISEMENT
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
May 23, 2014
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
View 5 Replies
View Related
Oct 28, 2008
Every day I pull a report showing a list of agents that committed an infraction. I want to be able to count how many times each agent committed an infraction. How do I do that? I tried Frequency and it did not work, or am I missing something?
View 5 Replies
View Related
Jan 6, 2014
I have a list of names all in one column, separated by row...
Example...
John Doe
John Doe
Jane Doe
Jane Doe
Jane Doe
James Jones
James Jones
I want to count how many times each name appears. Like this:
John Doe | 2
Jane Doe | 3
James Jones | 2
This is a very large list of names and I prefer not to have to type each single name into a formula because there are hundreds of separate names.
View 4 Replies
View Related
Jan 10, 2014
I'd like to be able to repeat a list of names X number of times.
Example: Repeat count = 3
Name1
Name2
Name3
Name4
becomes
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
View 4 Replies
View Related
Apr 4, 2013
I've got a set of data that I update once a month and the number of team members per team changes all the time. I'm trying to write a formula that basically says, if the date matches AND the manager name matches, count the number of team members.
In the attached sample if A2 and B4 are found in the data set, count the number of SalesReps they have. So I'm looking at Sarah for February 2013, she has two sales reps that sold something, but Katherine appears twice, so I'm not looking for a result of 3, the correct answer is 2. How do I write the formula?
A2 will look to the data range of A14:A23 and SarahK will look to I14:I23, but I want to count H14:23.
View 9 Replies
View Related
Dec 28, 2013
I have a list of names for my wedding and am making place cards out of scrabble pieces and need to know how many times each individual letter appears in the 150names to know how many of each letter to buy
View 5 Replies
View Related
Nov 26, 2012
I'm trying to do a countif that matches 2 names and returns the amount of times the 2 name matches is in the list. Ihave first names in column a and the surnames in column B.
And there are lots like so
John
Greenwood
Bill
Griffiths
Melanie
Griffiths
View 3 Replies
View Related
Feb 26, 2007
Got a worksheet that has list of football teams
Man utd
Man city
Tottenham hotspur etc.
in two seperate worksheet i download external data from 2 different website with some stats about teams. Problem is that theses websites user slighty different names for the teams ie. it will be man city from one source and manchester city from another. i need to somehow get the names to match up is there an easy way to do this?
View 2 Replies
View Related
Jul 11, 2014
im trying to look up data by a date and then between times.
I have wrote a formula in C6 but it does seem to work.
On the auto sheet, I need to lookup the date in cell B3 then handled in cell C3 and finally lookup the time in cell B6. Once I have this I need to look on the Data Tab in row one for the date then row 8 for the handled text and then finally I need it to look at column G to bring back the BETWEEN times.
View 3 Replies
View Related
Jan 26, 2009
My table has one column C with 3 possible values. Column D has either TRUE or FALSE. I am trying to count individually all the times when B = True (F4), T=TRUE (F5) and B/T =TRUE (F6) excluding the blank cells.
But the final goal is to display the total figure required to be answered, but as each question is answered yes or no subtract 1 from the displayed figure. My sumproduct adds up the "B" but does not match with a "TRUE"
View 4 Replies
View Related
Feb 11, 2014
What I need to do is work out the cost of call 1, to work out the call cost you will times the duration by the cost of the call on the charging codes tab. So if E2 = uk Mobile 02 (fm1) then search for a match in charging codes tab column B then get the cost from column C and times that by the duration on CDRS test tab column D
the cost amount needs to be in column I
a different explanation below..
if CELL e2 matches a column in charging codes tab column B then get the amount of cost from charging codes tab column C and times it by cdrs tab CELL d2 and return the result in CELL I2
View 9 Replies
View Related
Jul 2, 2014
I have a spreadsheet which has the following data;
Names Age Salary
Fred 23 $19,000
Joe 35 $26,000
Alan 45 $7,000
Richard 32 $25,000
James 19 $16,000
Ian 23 $28,000
Michelle 30 $45,000
I'm trying to list the names only where their age is "xx" or if I choose a certain salary, obviously there are hundreds of names, age's and salary information. How can I carry this out in excel ?
View 4 Replies
View Related
Jun 17, 2009
I need some assistance on creating a formula that will Match "W1 Sheet" Team number look this number under Schedule Sheet then list names under "W1 Sheet". I have added a simple sheet.
View 5 Replies
View Related
Jan 6, 2010
I am trying to write a formula that says if a name is in a column of names put a 1 otherwise put 0. Should be a simple If, match function. I have attached a simple example.....
View 5 Replies
View Related
Mar 19, 2014
I've been trying for a few time to make a sub, on VBA, that writes, in a specific column, " " or 1 depending on the date that is inserted. The thing I can't quite do is:
1 - I have a sheet where the different projects of a company are stored (name, starting date, ending date and person responsible) and where new projects can be added from a UserForm
2 - I also have another sheet were costs from the projects are stored (one line with cost 1 from project 1, another with cost 2 also from project 1 and cost 3 from project 2, etc) and where I can also add a new cost with another UserForm (this cost as also a date that is added, to when it happened)
3 - What I need to do is, on the costs' sheet I need to create a sub that in the last column adds me 1 if the date of the cost added is not a date between the starting and ending date of the project that it corresponds.
View 1 Replies
View Related
Nov 8, 2012
I have sheet1 that contains rows with names. I want to match those row names with the column names in sheet2, and then delete any column in sheet2 that does not match to sheet1.
View 2 Replies
View Related
Feb 18, 2007
I have two lists of names and I need to identify the people that appear on both lists. Often it is difficult to match the common records because a nickname may be used for either of the first names and not the other.
I've collated an extensive list of nicknames to help match the records, but I'm stumped on how to use it. I have a feeling that there is a sumproduct formula in my future. I attached a samples workbook.
View 9 Replies
View Related
Jun 21, 2007
I have two sheets of same names with different data in each sheet. I need to refer the name in the first sheet and match the same name in the second sheet and retrieve the start date. I have attached the work sheet sample for better understanding. I know I can use vlookup. But am not getting the results I need.
View 9 Replies
View Related
Dec 22, 2012
I have a table A1:A400, where each individual cell contains multiple names (e.g. A4 =Name1; Name2; Name3)
I am trying to match a list of names in row G1:G50, and return a value of 1 or True in another column if any of the 50 names appear in a cell of row A.
Attendees
Names to be found
Rep was there
Max; Joe; Mike; Karl; Jen;
Tod
True
Max; JB;
Mike
Max; Tod; Mike
True
I have been trying this as an array formula:
{=IF(ISERROR(SEARCH($G$2:$G$50,A2,1)),"", "True")}
But it only returns a true value for if it finds the first name from Column G.
View 3 Replies
View Related
Jun 10, 2013
See attached spreadsheet. I have 2 tabs. The current portfolio tab is feeding in from the computation tab. My formula in column C works for 2 securities, MSFT, and AApl as the name is identical in both tabs. I am running into an issue where the names are not identical.
Example: I want to sum both the INTC, and the INTC pref in the computation tab and have that fill in the current portfolio tab. Also, in the computation tab ticker 005930 ks should fill in the current portolio tab for samsung electronics. My lasst issue is in the computation tab where I want to pull in sony, and sony adr (multiplied by 1,500). total sum 1,5723,995,015 in the current portfolio for sony.
excel question.xlsx
View 9 Replies
View Related
May 7, 2014
I have two sheets. Sheet A (Receiving Log) contains all orders with NAME (D1) and QTY ORDERED(E1). Sheet B(Component Reject Tag) contains all rejects with NAME(D1) and QTY REJECT(E1). Sheet A and B are Many to Many relationship
What I am trying to figure out is:
Match the names from Sheet B to Sheet A and sum up the QTY REJECT from Sheet B divide by the sum of QTY ORDERED for the NAME from Sheet A.
Business would like to get QTY Reject percentage by each NAME (Supplier)
In a final table lets say Sheet C I would like to see NAME, QTY Ordered, QTY Reject, Ratio in which will allow my to perform a Top 10 suppliers of highest reject percentage.
Another note to mention, Sheet C presumably to have all the formulas as in Sheet A and B will be overwritten and pasted with new data.
View 10 Replies
View Related
Feb 10, 2014
(Matching names)where cell G and cell H in Data Sheet matches with cell A and cell B in template sheet , copy cell K on matching row in data sheet to matching name in cell D in the template sheet.
Could be as many as 50 rows of data in data sheet and only a range of D8 to D15 in template sheet.
Can send example but I cannot see where I have option to attach the file again : place_user.xlsx
View 1 Replies
View Related
Mar 5, 2008
I have document1, which is a main list of surnames names, which then show 'hours', 'employment number', and 'post', in the following columns.
In document2, I have a sheet with a selection of these names, and blank cells for the named columns above.
What i need to do is have excel search and match the name from document2, to the name in document 1, and fill in the above columns with the correct data.
is that some of the surnames will show up more than once in my main list - so if possible i would like excel to then search the first name (which is in the preceding column) and match both the first and second names to the main list - OR - to simply return an error so that i can spot, and input the data manually.
View 9 Replies
View Related
Dec 26, 2013
I'm having trouble at getting the overall fastest time to show up. There are tree racers in two heats. Also the times are set up like 0;00.000 because i cant get it to work like MM:SS:MS .
Daytona GTR34.xlsx
View 5 Replies
View Related
Nov 22, 2006
What's the fastest way to accomplish the following:
I have many of these in my code and inside nested loops, so they significantly slow down my code
jRow = Worksheets("Sheet4").Columns("O").Find("orange", _
LookIn:=xlValues, LookAt:=xlWhole).Row
View 9 Replies
View Related
Apr 9, 2012
I have an excel workbook with a dynamic dependent drop down list setup, works a treat on the first worksheet i set it up on.
However here's the problem, my workbook contains a good handful of worksheets each named to reflect the certificate on the page, i need the drop down list to work on each tab. problem is when i setup the index/match function it requires a specific sheet name to make it work i.e.
=INDEX(Table1,0,MATCH(PI!$C20,Table1[#Headers],0))
However what i need it to do is reference the worksheet name, as new ones get added and names may change, something like
=INDEX(Table1,0,MATCH(&ActiveSheet.Name&$C20,Table1[#Headers],0))
However this doesn't work, i do have a cell on each page that holds the Sheet name using the function:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
But I cant even get it to use that cell as a reference in my formula.
View 9 Replies
View Related
Jun 20, 2014
I have a large data (more than 50k rows) in a spreadsheet and i want to consolidate the information's.
here is the sample source data.
Source Data
codeamt1amt2amt3
123456$16.00 $0.00 $0.00
789011$0.00 $18.00 $0.00
123456$12.00 $5.00 $0.00
123456$0.00 $0.00 $7.00
111111$11.00 $1.00 $3.00
789011$22.00 $0.00 $0.00
and the output should be.
Output
codeamt1amt2amt3
123456$28.00 $5.00 $7.00
789011$22.00 $18.00 $0.00
111111$11.00 $1.00 $3.00
currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.
View 8 Replies
View Related
Jul 29, 2014
I have workbook #1 sheet1 that is linked to a master workbook sheet1. The master workbook has a bunch of sheets. I need to make copy of my sheet1 in workbook #1 and change the links from a sheet1 in my master workbook.
So for example, if my worksheet in workbook#1 is linked to master workbook in worksheet1 tab1, i want to make a copy of my sheet in workbook#1 but now have it linked to tab2 in the master workbook.
I know i can just highlight and do a find replace right? Is there a faster way to do this because i have a bunch of sheets.
View 2 Replies
View Related
