I have a column for suppliers names and a total column The suppliers names differ. How can I create a separate column to show the total of spent for each supplier. I'm not up on excel code
View 4 RepliesI need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedColumn O is a list of unique 1000 supplier codes
Column G is a list of 75000 imports (dollar amounts), each row has a supplier code.
I want to have a formula in Column P to sum the rows where the suppier code equals the supplier in column O.
i thought this was easier in 2007.
I am trying to add a leading zero to sum up the total time spent on a project. the data came back in this format :00:00 which does not allow me to sum up. I changed the format to HH:MM:SS but that did not add the zero.
View 6 Replies View RelatedLet's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx
I have a spreadsheet with these columns:Group_Name
Contact Name
Company Name
Contact ID
Situation:
The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached.
Goal:
I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
Frequency
Formula to show the final/total a numeric values appears in an adjacent column.
I am currently attempting (I've researched many posts on this), to count /show the final totals a duplicate numeric value appears in one column, in an adjacent column (example below).
I am currently using an array Frequency formula below (courtesy of the board) filled down in column Q. this works well to count the frequency of duplicate numeric values appear on the list.
Formula used in Column Q below
{=IF(C2>=0.1,MAX(FREQUENCY(IF(C2:$C$1000=C2,ROW(C4:$C$1000)),IF(C2:$C$1000C4,ROW(C2:$C$1000),""))))}
However my aim is also to show the final entry of a numeric value in adjacent column R, by displaying text to indicate this. My example below shows “final” in column R
NB: My list is sorted by column C to ensure all numeric values are in ascending order.
Example
Column C Column QColumn R
12567 1 final
15789 1
15789 2
15789 3 final
23456 1
23456 2 final
12678 1 final
18965 1
18965 2 final
I am trying to create a spreadsheet that will keep track of budget spent and budget remaining.
I need the values to take what is spent from expenses claim forms that are filled out,
Each budget category has a code and within that code there are sub heddings
I have an auto generated report that is exported into .csv, my dilemma is that it sorts both the account code and the date in the same column, like this:
9884A B Gerrish Ltd œ 577.270577.270
30/09/201231/10/20121865518655277.010277.01MAL/553260
30/09/201231/10/20121884218842264.260264.26MAL/553259
30/11/201231/12/2012195541955436036MEL/549200
so "9884" and "30/09/2012" are in the same column.
Some how i need to turn this information to say the below three invoices are for account 9884, into more of a usuable data like:
9884 A B Gerrish Ltd 30/09/2012 etc etc.
Obviously through out the document this repeats for each account, upto some 1000 accounts.
jas.xlsx
I need a formula that will show the total number of times "text" is entered into a column and when sorted by year that would only show the total for that year. The COUNTIF formula i'm using only works for the total, not when sorted by year.
View 9 Replies View RelatedI have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
In a worksheet of marks of students, i have entered grades A,B,C,D,AND E.Grades are entered in cells o3,AB3,AO3,BB3 AND BO3.
In BQ3,I want to get -in the range of O3:BO3
a)how many "A" are there?
It should display for example A=2,
b) how many "B" are there?
It should display for example B=2,
c)how many "C" are there?
It should display for example C=2,
d)how many "D" are there?
It should display for example D=2,
e)how many "E" are there?
It should display for example E=2.
In BR3, I want to get >
If A=10, B=8, C=6, D=4, E=2 then
display the total value for the grade letters.
Pls see the attached file for more clarity.
I am trying to add separate columns together to get a total count of a specific criteria. The formula I am using is:
View 3 Replies View RelatedI have month to month data for how many people have left an organization. I'm interested in tracking three things, how many of them left voluntarily, how many of them left involuntarily, and how many left within 90 days. The way to do it was to create a bar graph with one bar that would indicate total people who left and one bar that would indicate how many left within 90 days. However, I want the total bar to be made up of two separate bars (a composite, see this link for an example of what I mean Graphing- Bar Graphs ) one for involuntary, and one for voluntary.
View 3 Replies View RelatedI have a large amount of data that is from an events file. When I enter it into excel, it almost maxes out the row count (excel 2010) The data basically lists Event, Date/Timestamp, Call ID, and a few other details. Im trying to get a total of specific events and if they were successful or not, so the other columns arent of concern. For instance, there is an event called searchbyphone, a later row in the data will include searchresult found or notfound. The only way to pair these up is by the ID number which is unique per call.
So in the example below, ID 123-45-789a was a search by phone and successful (found). The Date/Time field is down to milisecond. If this were just adding up all the various events and then totalling the searchresult found/notfound, it would be simpler. However, Im only interested in certain events and found/notfound is used for many other events.
Using the data below, what is the best way to pull out an event (say the total number of searchby phone) and then know how many of those were found and not found (when the information needed is on 2 seperate rows and only the ID would be the same?)
To through a wrench into it, there can be several events with the same ID becasue the ID is unique to a call - a caller can search by a few variables. There will be the same amount of found/notfounds though with that ID. (meaning as a caller, I can do a few searches and all of these will be in the event file with the same ID) In this case, the number of requests (events) will match the number of results (found/not found).
What Im trying to accomplish is to get a total of searchbyphone found and not found, So in this example I would have searchbyphone found = 1 and searchbyphone notfound = 0.
ID
Event
Date/Timestamp
123-45-789a
searchbyphone
10/7/2013 12:52:38
987-65-432b
searchbycity
10/7/2013 12:52:39
123-45-789a
searchresult.found
10/7/2013 12:52:41
874-35-123c
searchbyzip
10/7/2013 12:52:42
987-65-432b
searchresult.not found
10/7/2013 12:52:47
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
i have a list of parts that adds up and shows a total at the bottom (great, works perfectly)
in the next column i have a percentage (margin) which again works perfect
ie.
Col A Col B Col C Col D Col E
1 ENGINE 4500 5000 11%
2 SURCHARGE 1800 2000 22%
3 OIL 1 2 100%
Totals 6301 7202 ??%
what i would like to do (forgive me if i have missed something) is for the total value in colum D to add up but for the % in column E not to be included in the total %
Is it possible to have a situation that when the word"SURCHARGE" is in the row then the % will not be added to the total %
In other words i need the surcharge price to be included into the final price, but the surcharge would give me a false figure so that needs not to be included in the total %
Attached are two files. I want to make the "ORIGINAL" file look like the "DESIRED_OUTPUT" file using VBA.
View 3 Replies View RelatedI've attached a sample workbook. Data Table 2 on the Data Tables sheet is what I'm after. Basically I have a formula that sums up quantity 1 (Q1) and quantity 2 (Q2) for all widgets at a site name and month. So for example, go down through column site name and column month and add up all of Q1 for site1 in the month of Jun 2014 (all widgets). This works but it shows duplicate totals which makes sense as there are mutliple widget types at each site. I think the example workbook explains it better. Note the Pivot Table sheet is an example of what I'm showing currently and what I'd like to show. While I understand that depending on how I set up the Pivot Table I can get similar results using either Table 1 or Table 2 but the other thing is that my users like to do some filtering of the data sheet itself without regard to the Pivot Tables.
sum quantity and show total for each site.xlsx
I created a spreadsheet to track my bowling scores by date by lane
Date Lane game 1 game 2 game 3 total
What I would like to do is show my total average by lane for the season. 33 weeks. From there I think I can graph it out.
formular to show a coinage total in a spread sheet im sure its to do with < > but new to this and dont have a clue any help apreciated. This is what i am trying to achieve.
excel formula If A1= 10 Then B2= 0
If A1 =7 Then B2 = -3
If A1=13 Then B2= 3
I'm fairly new to Excel (2007).
I have used a simple COUNTIF formula (=COUNTIF(C7:C207,"Name")) on sheet 1 to give me the total I want.
What I now want to do, is display this total on a different sheet in the same workbook (Sheet 3) without having to take all the data from Sheet 1 over to Sheet 3.
I am building a Pivot table and I need to show ONLY the Grand totals at the end of the table. In the example below I want to show only Total volume Stage movement at the end and not the subtotals in the columns.
Column Labels
Pipeline
Best Case
Commit
Closed
[Code]...
I have the following spreadsheet Columns on master sorted by Column J:
A--------------E-------------J
SUPPLIER----CARRIER------LOC CODE
BELGIUM-----HAM-----------BP
HYDRO-------MSC-----------BP
DAINIPPON---NNR-----------BP
BELGIUM------YM-----------BR
BELGIUM------YM-----------BR
HYDRO-------MSC----------CA
BELGIUM-----HAM-----------CA
HYDRO-------MSC-----------CA
HYDRO-------MSC-----------DE
DAINIPPON---NNR-----------DE
BELGIUM------YM-----------DE
BELGIUM------YM-----------DE
What I need too do based on the Codes in Column J is create a separate workbook for each different code.
I have a table of data with three columns. In the first column I have different values, but they can also repeat. How can I have VB look at my table and for every distinct value in the first column create a sheet with the value name and paste all the data into that same sheet?
View 9 Replies View RelatedHow can I code to create a copy of a worksheet in an excel file I am using a macro on, after the macro is done processing? In other words, when the macro completes processing, create a copy of the worksheet labelled "Output", and save it in a directory that the user chooses? Also, would it be possible to create a .pdf file?
View 9 Replies View RelatedI want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
View 2 Replies View RelatedHow can I work out the following in hours an minutes: -
Cell A1 = 12th May 2007 20:00
Cell B1 - 14th May 2007 14:30
or should I put the dates and times in seperate cells? I would like the answer to show 42 hours 30 minutes