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.
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?
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 ?
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 RelatedI 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 RelatedI 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 View RelatedI'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.
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 RelatedI 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 RelatedI 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.
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
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.
(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
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.
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.
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula...
=INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
I have several Campaign Names (about a thousand) and about 25000 categories (with ID's) to choose from and would like to assign the Category ID's to the Campaign Names without doing manual searches. My problem is that the names are not similar enough for vlookup and I didn't get any usable results with the fuzzy lookup add in either.
I will attach an example of both my Campaign and my Category file for reference. Example Complicated Vlookup.xlsx
I have a worksheet that has 2 columns on it 1 is numbers and the others are
names. I want to be able to export a group of names from one of my programs
and then have excel match the name and dump the corresponding number into the
spreadsheet. I beleive I can use vlookup but I am not certain
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code:
Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then
[code]....
Is there a place on the net where I can work on, or take part of real Excel
project ( for training purpose ). If not, do you think that is possible to make such a place were people can learn and practice excel working on simulation of a real Excel projects??????
I need to create a waterfall structure to analyze a real estate problem. How do I do it?
View 2 Replies View RelatedI need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.
The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.
I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")
I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7.
My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned $8137, my percentages for sales all jump to Row 3.
I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.
I am not quite getting how to test a value if it is an integer or a real.
If A1 = 10.4 I want to skip that cell, if A1 is 10 I want to process that cell.
I have 300 records in one table (A:I) and would like to create another table where column I is equal to a specific value. I understand that this is possible with an advanced filter, but I would like the second table to populate in real-time instead of needing to reapply the filter each time.
I have been trying to do this with conditional vlookups, but cannot get it to give me all the unique values. Everything comes back to circular references, which I still haven't been able to get to work.
Reasoning: I have created a fantasy baseball "cheat sheet", ranking the top 300 players. Column I is a dropdown menu for the team that drafts each player. As I update this throughout the draft, I would like my "team" to auto-populate in another table as I draft players and select my name in column I.
I have an API, which inputs real-time stock data into an excel book. Does anyone know how to make the Last Price cell change coloer (Greem=up, Red=donw) depending on whether the price moves up or down, relative to the last price?
ie, over the course of a minute a stock may price at 35.00 => 35.01 (cell turns green) => 35.00 (cell goes red) => 34.95 (cell stays red) => 34.97 (cell goes green).
VBA, conditional formatting, or any other means of doing this is perfectly acceptable.
I have 200+ XY pairs I want to chart and see a visual representation of their distribution. Then I want to cursor over exceptional points and see their identity. I don't need (nor want) data labels, disparate markers, nor a legend. I just want the points scattered on the xy plane. Also, the data set changes depending on user-controllable filter criteria.
There are two problems:
1. If I build it through the wizard as a single data series, everything is fine except that the charting process seems to replace the X value of the XY pair with a sequence number once I swap in a new data set, and ...
2. I seem to loose the individual point identities for cursor-over purposes.
The only solution I can think of for these issues is making each XY pair a one-point Series, but that's a hugely laborious way to do it. If that's it though, is there a way to globally load the independent data series and set every data point marker and color to be the same?
However, if I could do it as a single series, then I still have the issues above. Is there a way to prevent Excel from converting the X values to sequence numbers once a new data set is swapped in, and is there a way to identify the individual points for cursor-over?
The following code works to update the target sheet based on matching column names within the source sheet.
However, I would like to bolster this by not only updating based on matching field names, but also the primary key in column A. Meaning the data from the source sheet isn't always in the same order (based on primary keys) as the data in the target sheet. So I want to keep the order of the primary key in the first column and then update matching field names in column B through the last column where the primary keys match between the source and target worksheets.
Sub CopyDataBlocks()
'VARIABLE NAME 'DEFINITION
Dim SourceSheet As Worksheet 'The data to be copied is here
[Code]....
I need a forex excel spreadsheet that shows me the last 60 min of usd/jpy in real-time. It has to update every 60 minutes automatically.
View 1 Replies View RelatedI am trying to set up an IF formula for real estate operating costs. I am guessing that this is going to work best with an IF statement.
(Sample of the spreadsheet is attached)
I know this is wrong but it expresses what I am trying to do.
=IF(C5="Gross",B10=following year of B9 with month/Day being 1/1), if not B10=the folling year of B9 with same month/day)
1Cell C5 can either be Gross or Net from a drop down list.
2Cell B9 lists 3/1/2008
3If cell C5 is gross, cell B10 should be the following year from cell B9 but with starting with 1/1/??? As the month and day(year start).
B11 would then be the following year from B10
(following month/day pattern).
4If Cell C5 is NET, cell B10 should be the following year from cell B9 using the same month and day 3/1/????