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
How to list all data from different worksheets into one worksheet without manually copying and pasting it in a new worksheet?
For Example: I have 20 worksheets. In those worksheets are list of items that I bought from the different stores. I want to make a list of all items that I bought in one worksheet.
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 list of names in column B and either a 1 or 0 in column A as below:
0 A B
1 1 Bob
2 0 Chan
3 0 Lucy
4 1 Billy
On another worksheet I want to be able to list only the names with a 1 in column A. This must be done in another worksheet so I have something like below.
0 A
1 Bob
2 Billy
3
4
I am trying to get Solver to list a combination of invoices which make exact specified amount of close to...
attached is an example, maybe im doing it completely wrong, or other tools can be used...
I have a music folder on my computer with a TON of music in it. Some folders have sub-folders as well.
I need an easy way to point to a specific folder and pull ALL the names of the files within that folder and all of the sub-folders and put them on an Excel worksheet.
So a brief example...
Main Folder: Now That's What I Call Music
Sub-Folders: Volume 1, Volume 2, Volume 3, etc
And within each sub-folder is at least 2 more folders named CD1 and CD2.
I want to pull the name of every song in ALL of these folders into one collective list in Excel.
I am trying to develop an invoicing system for my company. Most of our customers will have their invoices formatted a certain way which has been relatively easy to solve, however one customer needs to have their invoices formatted in a special way.I have a sheet that pulls in a list formatted like this:
Date product order number kilos
07/4/14 48% cream 445600 1000
07/4/14 PHM 445600 1500
07/4/14 PHM 445600 1000
07/4/14 PHM 445600 2000
10/4/14 48% cream 445800 500
What I need it to display is the following:
Date Product order number kilos
07/4/14 48% cream 445600 1000
10/4/14 48% cream 445800 500
07/4/14 PHM 445600 4500
So you can see it is grouping the invoice by both product type and order number but grouping by product.
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 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] ...
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
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 tracking 50 food items (in column A) for 40 homes (columns B-AO) where each home gets none or varying quantities for each food item (ex: apples 7 for home B, 0 for homes C-L, 19 for home M, etc.). I can get grand totals for each item for all homes, but need to also list how many for each home receiving apples. Ex: 4 homes get apples (4, 7, 19, 1 respectively). SO I want my total sheet to have a column for each food item with the grand total "31" and the second column to show 4 + 7 + 19 + 1. The quantities change frequently so I need a formula I can use for each food item. Also I don't want "zeroes" included. What formula would I use to do this?
View 3 Replies View RelatedI have been given a worksheet which has 5000 rows spread along 13 columns.
There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.
I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.
Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?
