I have a list of companies, each company has it is own name and particular code.
I specify Cell D12 For Company Code and Cell E12 for Company name.
How to write code that write company name automaticlly in cell E12 when I write the company code in cell D12?
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?
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.
1*X*1+ 4*Y*1 + 1.5*Z*1 + 8*Z*(.77) + 5*W*(.77) + 2*W(.9)
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.
View 5 Replies View Relatedsort 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.
Reference table:
Email AddressDomainCompanyxyz@gmail.com@gmail.comGmailabc@hotmail.com@hotmail.com
HotmailAlpha@facebook.com@facebookFacebook
Result Required
Email AddressCompanyxyz@gmail.comGmailabc@hotmail.comHotmailAlpha@facebook.comFacebook
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 have excel 2007 and how to do a text split. For example, in A1 I have : Name American Company AP Code PI Due $0.00
Is there anyway to do a text split in B1 to just have:
American Company
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.
Attached is a sample spreadsheet expressing
First sheet:
A:[Name]
B:[DBA]
C:[Owner/Officer_1]
D:[Owner/Officer_2]
E:[Owner/Officer_3]
F:[Owner/Officer_4]
G:[Phone]
H:[TxDMV]
[code]....
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.
1017
1017
1027 Total
1051
1021
[Code] ...
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....
View 9 Replies View RelatedName 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
xxxcompany machine1,machine2
yyycompany machine3,machine4
Companies are listed at Column B Vertically
Machines are listed at Column F Vertically (opposite)
I needed a useful formula in order to extract the company name from the email address I have listed in my excel worksheet, thanks for any assistance
somebody@xzycorp.com, so just want the xyz corp
anybody@abcinternational.com and abc international
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.
example.xlsx‎
I have attached a sample sheet.
I need the forumal to look up a company name and return totals for all the numbers.
I wanted to create a Time utilisation tracker which should record time, taken for each task to complete.
when the employee clicks on Start button, the start time should show up on the sheet and when he completes the task he will manualy click on "Task Completed button " then the end time should show up in the sheet and the task details along with the start time and end time should reflect in 2 workbook.
when he clicks on Task complete button the data should reflect in workbook 2 and the current sheet should become empty for the next task.
I need to design a spreadsheet in Excel that keeps track of annual expenses for the company car, but have no numerical data.
The only information given is the labels needed to be used, such as main heading, date, mileage, petrol, maintenance, registration, insurance, subtotals, averages and main totals. I've designed the spreadsheet with date - subtotal at the top of each column, with total and averages on the left hand side, under all the months (so the answers to both the total and averages would be in each column)
I also need to write a formula to work out the averages of everything and the formula I thought would be right is giving me this error message: #DIV/0!
I have attache a file to use as a reference, the real file is over 10 000 rows long.
What I need is to be able to quickly make a summary of each company within a certain period (let's say monthly) and be able to calculate the sub total of the GST column. Every report that's created than needs to be saved and put into a seperate file e.g 'TFQ Sep 07'. I understand I need to use a Pivot Table but can some one just give me quick example of how I would do this?
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'm trying to calculate the amount of money invoiced per company per month. In column B I have the date the invoice was issued. In column E I have the Company name. In column G I have the amount invoiced.
View 6 Replies View RelatedI 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
View 3 Replies View RelatedI have a question regarding deletion and replacement in a new field. ie. A1 reads dave.jones@microsoft.com. I want A2 to read microsoft. What is the code to delete "dave.jones@" and ".com" to be revealed in a new field.
View 4 Replies View RelatedNot exactly what year of Microsoft Excel we have at work buts its between 2007 to 2010.
Doing our weekly KPI on a friday is super stressful and if there is a way so multiple users can edit the excel spreadsheet at the same time?
Because with our excel spreadsheet only one person can edit the document at a time very frustrating at the end of a day on a friday.
Is it just Microsoft not allowing more than one editor at a time?
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
we've been using this spreadsheet as a timesheet since May of 2002. Recently in the past couple of days when we try to update an employee's time and date of work done by clicking on an update button on the first Worksheet, an error (Runtime Error '1004' Method 'Range' of object '_Global' failed) pops up.
Clicking the 'Debug' button opens a window up with this information:
