I need to map two lists of company names. 1st list is master list having around 1lakh company names. 2nd list have around 500 records which i need to manually match bcoz index and match functions just dont work.
What i currently do is copy a part of company name from list2, go to list1 and apply a contain filter and if any matches are found, take the closest match to list2.
This is a tedious task for 500 companies as it involves to many mouse clicks. We just need to paste a part of company name in search box and a contain filter is applied to list 1.
I'm trying to match stock portfolios of mutual funds with stock indexes to find out how much the portfolios deviates from the indexes (active share).
The deviation is simply calculated as the percentage of the portfolio holdings not included in the index.
The data I have is 10 years of monthly portfolio weightings for 30 mutual funds and 8 indexes, organized with company names in column A and the percentage weight for each company in each month in the adjacent rows (see simplified screenshot). I want to match all companies with all indexes.
Twofold:
1)The mutual fund data comes from one source and index data from another. This means company names are not spelled exactly the same. 2)Within the mutual fund datasets, a company might be listed more than once with different names. The different names refer to different periods in time, so what should be matched with the index is the sum of these columns.
The specific task that I need to accomplish is to match company name in the portfolio with the index and then return the absolute difference between weight in the portfolio and the weight in the index for each month. (see screenshot).
Hence the result I'm looking for is a matrix of company names (of each portfolio) in col A and the absolute difference between the portfolio and the index weight in the adjacent row.
The best solution I've come up with is to device a list of all the portfolio company names, including "doubles", and then for each index, add these company names and copy the row weightings. This I'm not particularly happy with because index weightings would no longer sum to 100 and it would be difficult to check for errors. Also, it would still take a lot of time.
I have a 97-2003 spreadsheet with approximately 22,000 lines of seperate companies. I need to know how to insert a blank line between the company names. I have been using the "Right Click - Insert" method but soon realized there must be 2000 different companies. The Company Name column is A.
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.
I should also point out that most company names appear multiple times in column [C7].
a) Count the unique company names in column [C7]...
b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records
c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records
Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
I have a nested if formula that isn't working and I know there is a real simple fix. Here is the situation: If Company A (A1) and Company B (B1) both have indices greater than 125, I want that to show that in my recommendation. For example: I used if(A1>125&B1>125,"Company A and Company B",if(A1>125,"Company A", if(B1>125,"Company B","None"))). For whatever reason, the first nested if statement isn't working correctly. what I am doing wrong with this if/and statement?
My boss has given me a spreadsheet to put together for him, unfortunately i am not really an expert on Excel and i am at a loss as to how to do this. This is what he wants:-
He wants to be able to cross check all the names in A with the names in F, and if any matches then also compare Column B with Column G. Again, if any matches then finally check Column C with Column H and pull the results
I would be extremely grateful if someone had an idea on this and could possibly give me a formula that could do this, or am i asking too much from Excel?
I have two different lists of names. One is about 3000 names long and the other has about 3500 names. I want to identify the 500 people that are not in the first list that are in the second. If there anyway to do this? I was going to put them in A-Z order and then go through and using an IF(A=B,"yes","no") and go through and insert new cell when ever it says no till it says yes BUT the functions start messing up when I insert a new row and it's taking way too long.
In Excel 2003 I am trying to compare two lists of names on the same spreadsheet to determine which names are different between the two columns of names. What is the easiest way to do that?
I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list 2. Sheet2; how to remove "B000" or "B00" from the list 3. Sheet3; how to highlight difference or find out the difference between to lists
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
I have a spreadsheet with thousand company names that I would like to extract and hope there is way to not do it manually. The data is organized in each row like this:
5;"Novo Nordisk";"Medicinal og biotek";"66346000";"17097000";"31499"
I would like to only keep the company name, in this case Novo Nordisk and delete the rest of the information. How do I do that ?
Here is the Scenario, A friend of mine who works for a Trucking company asked me this Question; This is How their company calculates His Pay for the Trips that he Makes. @ first 100 Meter + Loading A Value of 1 Which will be Multiplied by the weight of the Load “L” and then a Flat Fee of “X’, And Off course The weight is not of any Issue for us for time being.
@ first 100 Meter + Loading A Value of 1 * X @ Next 500 Meter a value of Max. 500-100=400 => 400/100= 4, then A Value of 4 * Y, Then if Total Traveling is 350 Meter, There we will have 1*X*L + 2.5*Y*L And again L is not important and for time Being Could left Out. @ Next 10 Kilometer A value of [Number of Kilometer - .5]*Z Then Max would be 9.5 * Z @ Traveling of Over 10 and Less Than 30 Kilometer would be [Number of Kilometer – 10] * W Then a Traveling of 27 would be 17 * W @ Over 30 Kilometer a Value of [Number of Kilometer – 30] * V And Finally all these would be added up accordingly.
So far so good, and There is no Problem and Easily I can Use Excel To Calculate Every thing for me Just by Entering the Total travel amount and the Weigh of the Load, Using If, And Vlookup. But Here it gets Complicated, They have these Following additional Conditions;
1: If the Traveling Road is a High way or Major Paved Roads Then all the above Values would be multiply by .77 2: If the Road is a Semi Paved Road then all the Above Values would be multiplies by .9 3; If the Road is a Dirt Road then all above would be Multiply by 1
For Example if the Traveling is 17 Kilometer and 2 kilometer is Dirt at the Beginning, and 2 Kilometer is the semi Paved at the end and the Rest are Fine Paved Road.
My company has a lot of file excel. I usually use Google driver, LAN and a lot of the other ways to share file to all member in the company. But so, it is time consuming very much and not protect information in my execl file.
sort out the email address as per the company name using Vlookup or any other formulae other than using text to column on "Email Adress" and then doing a Vlookup from the reference table.
I have two sheets that both have all the same company names on them but one of the sheets doesn't have ALL of the company names. So, one sheet might have 2500 rows with company names and data specific to that sheet, and the next sheet might have 2450 company names (all match up to the first sheet, but 50 are missing), with different data.
The first sheet is A through L, the second is A through K.
Company Name and Name will match EXACTLY on each sheet, but the second sheet won't have quite as many. So, if possible, I would like to, where column A in matches in both sheet 1 and 2, move or copy all the data from sheet two into the columns on sheet one that come after L.
I am trying to match up company codes across three different columns. I am inserting rows to get them to match up. However, there has to be an easier way.
for instance "Dave's electric" and only those charges that are between Jan 1st and Jan 5th on a new worksheet. Then do the same for "Girlz Clothing". I know some VBA so I would be willing to go that way too....
Name Qtr Year Org 112007 Org 222006 Org 332006 Org 442006 Org 542007 Org 612006
I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.
The code should loop through a range of constant data (Region Name), until the Region Name is blank.
Dim OrgCell As Range Dim OrgTargetCell As Range Dim RegionCell As Range Set OrgCell = Sheets("DATA Removed").Range("A2") Set OrgTargetCell = Sheets("Overdue").Range("A3") Set RegionCell = Sheets("DATA Removed").Range("C2") i = 0
OrgCell.FormulaR1C1 = _ "=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)" Do OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value i = i + 1 Loop Until RegionCell.Offset(i, 0).Value = "" End Sub
I have a list of machine manufacturers in a comboBox and type of the machines they make listed on a excel sheet. When the user chooses the company from the combobox.list I want the machines types appears at the textbox below
I would like to ask how can I transpose this: COMPANY1WEBSITE1FullnameAddressCity, State zip codetel numberdescriptionCOMPANY2WEBSITE2FullnameAddressCity, State zip codetel numberdescriptionCOMPANY3WEBSITE3FullnameAddressCity, State zip codetel numberdescriptionCOMPANY4WEBSITE4FullnameAddressCity, State zip codetel numberdescriptionCOMPANY5WEBSITE5FullnameAddressCity, State zip codetel numberdescription
to this: COMPANY1WEBSITE1First NameLast NameAddressCityState zip codetel numberdescriptionCOMPANY2WEBSITE2First NameLast NameAddressCityState zip codetel numberdescriptionCOMPANY3WEBSITE3First NameLast NameAddressCityState zip codetel numberdescriptionCOMPANY4WEBSITE4First NameLast NameAddressCityState zip codetel numberdescriptionCOMPANY5WEBSITE5First NameLast NameAddressCityState zip codetel numberdescription
I have a workbook with 2 sheets; Forecast and Resources.
On the "Forecast" tabb there are 2 columns; Column A: "Company Name" and Column B: "Forecast"
The "Resources" sheet is for breaking the forecasted amount from the Forecast sheet down to the various resources who will be generating the forecated revenue. Column A lists the Company name and each column after that lists the resource.
Unfortunately, entering the data requires bouncing between the 2 sheets.
Question:
This request has 2 parts.
Part 1) Any time a value is forecasted for a company listed on the Forecast tab, I would like the system to automatically copy the name of the company (column a) to column A of the Resource tab. (initially the row 2 as row 1 is a header, and then subsequent entries to the next available row)
Part 2)
Any time a value is forecasted, a window which lists a predefined set of resources will appear allowing the user to assign an amount to each of the resources therein AND then, when the window is closed, that data will be used to populate the corresponding row/coumn on the Resources Tab.
The file that I have attached has the run sheets of truck drivers, which company they are working for and how much money they are owed (the money they are owed is in the GST column).
What my company needs to do is to create a tracking sheet for each month for every company with a sub total of how much we owe them.
As an example, in the file I have provided, if you filter all the dates in between 1/9/07 and 1/10/07 (so all the dates for September) and then filter Company to 'TFQ'.
So what you see is all the jobs that TFQ has done for our company in September. What we want to do now is create a sub total at the end which calculates the GST column and then save it to a file, which we can then e-mail the company to say 'This is how much we owe you, is this correct'.
But since as you can imagine, filtering and adding the sub totals is a long process, is there a way to
1) Have some kind of formula or code which would filter every company within a certain date range adding the sub total and save it to a folder named 'Sep TFQ' for example. This would be the killer if it can be done.
The reason being is because the real file is much larger than i could fit here, and it would be a long process to do this manually, that is filter the company, type in the sub total, and save it to a file, as this is done every month.
I am working on a project. As part of project, emails were sent out to an experimental group for a cost survey. I have to combine data and do analysis now. I am given two excel spreadsheets (sample of both attached).
In sheet 1, column B contains email name. For instance, first email name (Row A2) is ‘96 '07 Bustello Third email name (Row A4) is 90-pt Panda Express '10
Based on this email name, I need to create a new column that contains only company name i.e Bustello against row A2, Panda Express against Row A4 and so on.
I thought of using ‘Text to Column’ function in the beginning. However, since there is no common character in all email names and no fixed width at which company names are embedded in email name I could not use it.
For now, I am doing it manually. I have 10,000 email names and it is becoming hard to do it manually.
Although I am given another sheet (sheet 2 of sample), which has two columns: Company number and Company Name, there is no common variable that I can use to do VLOOK UP therefore; I am not sure how to make use of that sheet.
I've also attached an example excel spreadsheet of how I was thinking the first page would lay out.
I work for a company that has multiple properties around my region here. We all deal with similar companies and temporary workers, and what i'd like to do is set up an online document (using google docs?) where we can all go online with our login, search the document, and even add entries, as well as comment and rate on entries already existing. I may be stretching the boundaries of what excel can do, but I really don't want to start diving into programming something.. if it can be done this seems like the easiest way to accomplish this task.
Here's a quick summary of what I'm thinking (and use the attached as a visual example)
the first page will be the only place people will really "look" at. On there it will have searchable areas to load a list of names based on criteria (name/partial name/letter, as well as maybe a drop down or check box of specific standards the company or worker deals with like lighting/audio/video/etc)... In my mind the company or worker could specialize in more than one of these areas.
once you hit the search button, it will populate a list of names based off of that search. Inside of that scrollable box will be the name of the company/worker, and some ratings (it could be stars or even a number percent) - the name itself would be clickable, then next to that would be a button to rate the company/worker, and a button to comment. If you click on rating it would pop up a box to add a percent. that percentage would add a field on another page and the total average rating is what is displayed. If you click on comment, a pop up box would come up and they would be able to add a quick comment on that company or worker. Once they click add on that it would add/append that comment onto another page in the database with the time/date stamp.
In the next section, when you click on a name from the search, that contacts information will pop up in a pre-defined format. Clicking on a different name would replace all of the information with the new contacts information sort of "dynamically" loading the content each time from a separate sheet that has all of that information. Nothing in here will be clickable, just a nice laid out viewport of the information that's been entered for that company or worker with address/phone number/rating/etc.
Then finally in the last section would be a scrollable box that would pop up all of the comments that have been added for that person with a date/time stamp for each comment.
As far as rating system.. i was beating myself up a bit about how to do that, but ultimately not many people would have access to it so I'm not worried about individual hits.. as far as I'm concerned people can rate more than once, it would just add to it and it would average those ratings.
