I want to make a crosstable (7 columns X 6rows) but the thing is that I only have the total number for each row and for each column, how do I do to get the whole numbers of the crosstable?
View 9 Replies
which formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
I have a spreadsheet with Company A's part numbers, then the number for the same exact part from their rival company or competitor. When searching on our website I need for the customer to be able to find the part even if we are out of stock of Company A's part, we can let them know we have company B's part and it is the exact same part. So I am cross referencing the numbers.
In my spreadsheet I have all of company A's parts in column A and their competitor parts in column B. In another column I have cross reference #'s I did with the concatenate function. Where it gets tricky is one part can have 50 or more numbers associated with it. So far, I have been doing the concatenate for each one. I haven't gotten very far as you can imagine. Is there a way I can have it check a column, then when it finds the same exact numbers have it put the numbers from column B all in the same row seperated by a space(my delimiter)? See image at link. I couldn't figure out how to post the spreadsheet. Maybe I am too new to attach files.
[url]
so in the example above, cell E11 has company A's part number, it's own number, then the number above it and the numbers below it. It has every number associated with 74679142.
If i have two list of numbers one list in cells A1:a50 and one B1:b50 i want in c1:c50 to see me the same numbers
Also if i have more lists of numbers a1:a50 b1:b50 c1:c50 d1:d50 e1:e50 then in row F show me the numbers that they are in all lists
First as said in title i have no idea what these kind of tables is called in English and i didnt know how to formulate me when it came to the title (so hint me with a "right" name for this kind of table)
To the point i have alot of these cross reference tables. What i mean is table that have a horizontal titelbar (row) and a vertical titlebar (column) and in the table you have the diffrent stats or whatever that can be within it. Im having a really hard time expaining this so im just uploading a excel file with a little explanation of what im after.
I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid.
The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....
create parts cross reference table?
View 1 Replies View Relatedi had a subtotal sheet with more than 5000 rows of data.i applied subtotals for my data.i just want to serial numbers for each of the subtotal.what is the easy way for numbering my rows with sub total.
My subtotals are in column A.i inserted a column before it and want to show the row numbers which contain the subtotals.
Keeping track of racing point scores and I am trying to figure out a formula that will allow me to take the top 6 out of 8 values and total them. We compete in 8 races but the total points standings at the end of the season takes the best 6 out of 8.
For example
Racer 1 100 200 150 90 300 250 210 95
I am looking for a formula that will give me a total of the HIGHEST 6 scores. I tried the =max but that will only allow you to get the HIGHEST value.
I’m creating a spreadsheet (attached) that needs to divide up aisles in a warehouse into sections, so that each section has an equal (or as close as possible) amount of work. The “To, From” columns are aisle ranges and the “Moves” column is the amount of work in that range. I need a macro/formula to add up numbers under “Moves”, but only up to the point where the sum is as close as possible to the number in K2 (variable). It also needs to look at the number under “sections required” (I2) (variable) and then split the aisle ranges into that many sections.
View 2 Replies View Relatedcolumn A contains numbers. Can I use solver to tell me if there is a combination of the numbers that total a given number?
View 4 Replies View RelatedI'm working on an excel for work. It lists the days on the column farthest left, and with some variables calculates the total number of tons we have for the day on another column. For part of it I'm wanting the program to calculate how many tons we process for each day. The problem is that each days will have a different number of entries. For example:
June first has 6 entries
June second has 3 entries
June third has 4 entries
So I'm wanting to make another column seperate from the original data that calculates total number of tons from the entries for the day without saying cell=sum(the 6 entries from the first), cell=sum(3 entries from the second).
I'm not sure how to go about this, I thought if I could get a recurring if statement that would test all of the entries for the date, and if it matches sum them up. I'm just not sure how to go about it.
I need to total how many '1's or '2's in column A by each month ( Date Format 01.04.07) in column B.
Have tried to use the following formula to count all the '1's in May, but I cannot get it to work.
=SUMPRODUCT(MONTH(A1:A100)=05)*(B1:B100=1))
I do a lot of work in excel to do with accounts and this often needs checking against sage. When the invoices/petty cash sheets are put into sage the total amount is put in, but in my spreadsheets I need to split the reciepts. So I was wondering if there was a formula/VBA code, that if I only knew the total of the invoice would find which cells added up to this total?
View 4 Replies View RelatedIn Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
I am attempting to create a chart from data generated outside of Excel 2003 SP2, that has been Copied and Pasted into Excel. There is no metadata associated with this data. I have created a list with the data once it has been pasted into Excel 2003 SP2.
The Workbook has five (5) worksheets, all with the default names (Sheet1, Sheet2, etc.). I paste the data into Sheet1 and reference this from the other worksheets. Sheet2 shows the charts for all the data. Sheet3, Sheet4, and Sheet5 are each for different regions, North, South and West respectively. I would like to enter the formula into Cell: Sheet5!M5 so it auto populates with the appropriate and correct information. Sheet1 information is as follows;
Column B has a header of "CreateDate" and is in the format of; 4-Jun-07
Column O has a header of "Business Unit" and has three (3) possible answers, North, South or West. **I have attached a screen shot of the two colums used named, "Columns.Used.png". I have hidden the columns in between as they contain confidential information.**
I would like a formula that gives a total of one if both criteria are met. The criteria is as follows;
IF Column B on Sheet1 equals Cell L5 on Sheet5 AND Column O on Sheet1 equals West THEN the total should appear. **I have created and attached a sample spreadsheet "Sample.Data.zip"that contains only basic data on Sheet1 and Sheet5 as those are the only sheets relevant.**
If a red number to the right shows up anywhere on the left black numbers then I want a total in column G. I want it to automatically total the matching numbers. How can I do this?
View 3 Replies View RelatedColumn C contains zip codes.
Column D contains # of adults in the household.
Column E contains # of children in the household.
How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?
If you have a single column of random numbers, is there a way to give Excel a total amount you are looking for, and have it choose which numbers in that column total to that amount?
View 2 Replies View RelatedI have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.
What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.
I was given a spread sheet with a number of payments on it, I was asked to take the "four" potential payments and only show one total payment. There are 2900 lines in my file and as you can see from the example the scenario repeats it self with the "four" payments all the way to the bottom. I would like to be able to add H2 to H5, total that number in J2 and delete row 3,4 and 5. This then has to be continued all the way down to line 2878 where I could then run a macro I have to delete blank rows cleaning my spreadsheet up. I would take a looping or user controlled macro (ctrl +) so I don't have to do this manually.
Sample_total.xls
I am working to develop a spreadsheet that takes user input (from a barcode scanner, that issues a hard return, limiting my information to a single column) in the following order. User ID, this is an Alphanumeric unique ID (AA1AAA) or user id. Followed by a pick ticket ID the value of the pick ticket ID will always be above 100000 (ie 123123) followed lastly by the number of lines on the pick ticket id (this number never exceeds 15)
So an example data set would be:
AA1AAA - User ID
123123 - pick Ticket ID
7 - Number of lines on the ticket
123124 - pick Ticket ID
10 - Number of lines on the ticket
123125 - pick Ticket ID
6 - Number of lines on the ticket
AA1BBB - User ID..........................
I am trying to set up a football league table. Iam ok with most of the formulae to arrive at a points total. However when the points total of two or more teams is the same then the final position is governed by goal difference.
View 11 Replies View RelatedI have data which I have plotted in a stack bar graph. I have then got the data in a table below the graph and it is all good. I would like to put a total on my data table so the user can see the total value of the stack bar.
I can't see how to get a total into the data table using the chart functionality. I have got a total in my data series but including this in my chart data just corrupts my graph as it doubles the height of it by including the total along with the data that generated the total.
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 creating a data base to track Planner compensation. Many of our accounts have 2 planners on the same case. I am trying to learn how I can create a single list of all the planners (from "Partner 1" and "Partner 2") headings which will then total their compensation in a Pivot Table weather their name is under the "partner 1" heading or "partner 2" heading. See the attached example.
View 2 Replies View Relatedtitle says it all - I'm sure this is possible but can't figure it out
View 4 Replies View RelatedI need to add some HH:MM values to give a total general value, its for a staff rota, i need total values for the hours worked per day/week.
eg. three 8 hour days in 08:00 format to give a total of 24
Need a formula to calculate a absolute total from a column of totals.
I thought this was simple, but the formula I made didn't work.
Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
View 4 Replies View Related
