I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
ie:
asset no
description
received by
day
[Code].....
I have a table with average temperatures(120,125,130...220) on the vertical axis and equipment sizes across the horizontal access (size 1,2,3...11). At the intersections is the amount of heat given off the equipment. This table is used as a reference for another sheet in the same workbook.
Basically what the sheet is for is to look up the average temperature of a piece of equipment (given the size and exact heat given off). In order to do this, I need to interpolate using the 2 closest energy values and their associated temperatures. There's a better explanation in the file and it makes for sense when you're looking at it. Attached is the table.
Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
I have a static table that contains a list of all posible components for a particular product that we sell. The sheet successfully calculates the qty. of each component required for a particular install.
What I would like to be able to do is to create a new list able from the static one that only shows the components that are needed i.e. those that don't have a zero qty count.
I have seen some examples that come close on YouTube but can't find anything that does exactly this.
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies View RelatedI have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets.
On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does.
I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
I would like a formula that will take the info from 2 cells (not adjacent to each other or in the same row) and then look these up in a table giving the answer in the 3rd column somewhere else on the worksheet.
View 5 Replies View RelatedI've got an excel worksheet that contains a list of buildings and their associated control valves for water supply (subset of table attached).
I'd like the user to be able to search by building name to display its associated valves and control information. I realize I could use the list function on the worksheet, but would prefer to set it up as front page to the workbook where data is displayed in a nicer format.
I guess my question is if there any way I can set up another worksheet with a drop down box that allows the user to select a building or type in the building name and have its corresponding data display?
Im using a table so I can sort diffrent catagories and I'm trying to put other functions to the right of the table I noticed as I change the sort on the table is moves the information outside the table as well is there a way to seperate that information or do I just need to move it above my table?
View 2 Replies View RelatedTable1 (TeamA)
Table 2 (TeamB)
Name
Date
Name
Date
Table 3 (TeamC)
Table 4 (TeamD)
Name
Date
Name
Date
Given above is a single Sheet in excel with Four Different Tables for each team. I would like to scroll and enter data separately in each Table.
Say for Example Table 1 Team A
Scroll through the rows (1-100)
Enter Data (in any row)
Freeze panes at header
Likewise for all the tables separately. I had tried using split panes, but that is not what i am looking for.
How can i count "how many "A" for period date in table and date out table in this case"
Date inDate outresult Count
05-Mar08-MarA ?
05-Mar09-MarB ?
06-Mar07-MarA ?
07-Mar08-MarB ?
07-Mar08-MarB ?
08-Mar09-MarC ?
05-Mar08-MarA ?
05-Mar08-MarA ?
I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:
1) the personal data should be copied repeatedly.
2) Each Group data should be copied next to personal data.
3) The group's name is mentioned at the top of the datasheet.
I have attached a sample workbook for your kind reference.
I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example
If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
I'm having an issue and I've tried thinking of all the work arounds and haven't come up with a usable method. I built a form that pulls info from a Pipeline table. I wanted to keep track of all the people that make edits on a separate table. Is there a way to take the original info and paste it on to another table by way of a click() and allow the others to make edits on the fly afterwards? This has been bugging me all week.
View 3 Replies View RelatedI'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies View RelatedI am using a pivot table to summarise information with a list of tonnes and sources from civic amenity sites. All was fine until I got to this month to do monthly reports. The pivot table appears to be treating what appears to be exactly the same categories of "CIVIC" and "TRANSFER" differently. By this I mean that although the values in the column appears to be "CIVIC" or "TRANSFER", some get treated differently in the pivot table making it pretty useless. I have made sure that all spellings of the words are the same. there are no spaces before or after the word e.g " CIVIC" but to no avail!
View 4 Replies View Relatedi have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
View 4 Replies View RelatedI have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
I have to prepare sheet 2-sales for my job and I cannot find the way to fill the price and sales results depending from the three other inputs required of the table.I belive we will need the functions addif, match, & others but still can't make it. Another tag when there is no result it needs to show "No existe producto".
View 3 Replies View Related100% Bran Nabisco 70 130 10 5 6
AlI-Bran Kellogg 70 260 9 7 5
All-Bran w/Extra Fiber Kellogg 50 140 14 8 0
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?
Code:
Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long
LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row
[Code] ......
I'm trying to set up a macro which will import data from one worksheet to a master sheet. I need it to copy the information into specific columns but not overwrite any existing information which is already in the Master Sheet, but I don't even know where to begin.
Just so you're clear on exactly what it is I'm trying to do... I have a Master Sheet which lists all of our suppliers prices, margins etc etc... However, when we use a new supplier we send them a greatly condensed version of the Master Sheet - We call it the Supplier Sheet (no big surprises there)!
When the supplier sends it back to me I have to type it all out manually which is kinda time consuming. I'd really like to set up a "push button" system which allows me to simply drag the Supplier Sheet into the workbook, add the info into the Master Sheet, then be able to delete the now useless Supplier Sheet.
(I have attached a test copy of the file - all of the columns in blue are the ones which need the data adding to).
how I could create the following...
I have a table that is 4 columns x 178 rows (varies by row)
Columns are :- Name,sold,returned,%
I want to reate another table that is 4 columns (same) x 20 rows.
The twenty rows showing the worst % figures.
This isn't a one off otherwise I could just use the data sort.
I know one way would be to create and use a macro but was intrigued to find out if it could be done by perhaps some sort of array formula etc.
So I've got a workbook with three main sheets: Pipe, Fittings, Report. In the pipe sheet I've got 8 charts that are all the same but they're for pricing different types of pipe. I want to assign each line in all charts a category and then in the reports tab have a chart that will add all the prices from all charts in each category. I've tried using the VLOOKUP function but I can't seem to get it to work. I can attach the spreadsheet here if that would make things easier.
View 5 Replies View RelatedI'd like to create a workbook to track milestones of different projects in a calendar view. For instance, the first worksheet would have a table where I can list projects (Project 1, Project 2, etc.) in the top row, and milestones (Milestone 1, Milestone 2, etc.) in the first column. Then I'd populate the table with dates. The second sheet would be a calendar view that shows the milestones of each project under respective days. As an example, I'd have something like "Project 3 - Milestone 1" under January 1, 2014.
PS: I'm using Excel 2011 on Mac OSX.
I have to create a 12 row 14 column table which will be printed out and put onto a (around) 20 by 20 poster board...so it has to be HUGEEEEEE. How do I do this, all the boxes inside the table I want to have control of meaning size and stuff. I will have to write in all boxes.
P.S: Yes. I know you guys prolly think im a idiot but this is my first time and my due date is appraoching fast, if anyone could help that would be great.