I am trying to get a table that is created based on the numbers i have.
eg i have the following data in cells A1:B4
1 List 1 1.00%
2 List 2 2.00%
3 List 3 3.00%
4 List 4 4.00%
Ineed a formula that will look at them and put them in highest order first in cells E1:F4
1 List 4 4.00%
2 List 3 3.00%
3 List 2 2.00%
4 List 1 1.00%
I think i need to use an index but don't know how
I have a Pivot Table with 10 columns of data (numbers with a value of 0 to 1000) and a Total per row - all okay (1500 or so records).
For each row, I need to add the highest 3 values from the 10 columns to make a new total. Is there an easy formula I can add to the Pivot to do this?
I want to create a table that updates automatically with the name & highest score top.
I.E. currently I have :-
Dates starting in A4,
names B2 : K2,
scores B4 : K4,
Not too fussed about having the date in the results table but i'd like something like :-
Scott 4 points,
Geoff 7 points
Tony 2 points,
I would like a table to automatically sort a table (preferably on sheet 2) so it looks like :-
Geoff 7
Scott 4
Tony 2
Is this possible ?, I only have 6 rows of data so far so it can be re-arranged if needs be and data will be added each week.
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 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 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 attempting to put together a script which in simple terms finds the 'Highest and Second' highest activity and associated FTE for a given sub group.
To be more specific:
On the "Slide 1" sheet look at the values in column J starting at row 7 until blank;For each value, then search the "All Monthly Direct Activities" sheet in column C for the same value, excluding the 'Subtotal' rows;Where a match is found, compare all the values in column E for that sub group;And Find the highest and second highest figure;
Once these are found copy this figure and associated value in column B and paste onto the "slide 1" sheet.
I'm not particularly well versed in writing VB, but I've been working with the code to see if I can get this work, which I have been unable to do.
I have attached a file which may perhaps provide a greater detail than my description of the problem containing the 'Source' and 'Destination' sheets.
If I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?
View 9 Replies View RelatedI'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 Relatedhow 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.
I've got a table on a worksheet (daylies) with daily data, dates in A6, A7, A8, ..., various data in columns B, C, D, ... .
Now I would like to make a subset table on a separate worksheet (weeklies) with just weekly snapshots, i.e.,
snapshot from daylies off row 6 on row 2 of weeklies,
snapshot from daylies off row 13 on row 3 of weeklies,
snapshot from daylies off row 20 on row 4 of weeklies etc.
Is this possible or do I have to use the sledge hammer technique of copy and paste?
I have an array which I would like to extract the information and put it in a table in a spreadhseet. Does anybody knows a faster or better way to do it different than using a for next?
View 2 Replies View Related1) I want to create a pivot table in a new tab that has both the prefix “ENG” and the date of creation as the new name of the tab every time a new table is required, using the example headers highlighted in the attached file.
2) Then if possible format the table as in the example, i.e. bordered cells for all the data and the dates only aligned to the right. Example in tab “Eng 01 05 09”
No 1 is the most import question I would like help on. Formatting is not crucial. This is only to make the table easier on the “eye of the beholder”, I am writing this spreadsheet for people who have less knowledge than myself and would like to keep it as simple as possible, (run a macro and get the results).
I have a piece of equipment that dumps data into excel that looks like this:
#1 Asset
#1 Widget
Component1
Horizontal
0.017893
2/21/2014
5
[Code] .......
This order goes on and on for hundreds of rows with different widgets and different components.
I am trying to find write a macro that will sort the data to be summarized into a table like this:
#1 Asset
#1 Widget
Component1
Horizontal
0.017893
2/21/2014
5
[Code] ........
I have a list of variables, each of which has an associated list of possible values. Simple example:
Fruit (apple, pear, peach)
Snack (Snickers, peanuts, crackers, pretzels)
Now, I need to build a table that for each line has every possible combination:
Fruit Snack
Apple Snickers
Apple Peanuts
Apple Crackers
Apple Pretzels
Pear Snack
Pear Snickers
Pear Peanuts
Pear Crackers
Pear Pretzels
Peach Snickers
Peach Peanuts
Peach Crackers
Peach Pretzels
My table will have over 18000 lines, so building it from scratch will take time...is there an easier way (I'm real rusty on VBA, so that would take a while just to figure out the syntax again).
The spread sheet I work with will not post info on the second sheet automatically. So I'm in the process of trying to figure that out. The second page is basically giving a percentage of parts the person did compared to what another company would pay that person per hour to do at 7.42 an hour. I need the second page to take all the info on the first page and figure out the %. What it does to get it is, adds the numbers and divides by the total to get average and then compares it to 7.42 and hour.
Also, if i could get the Name boxes to be auto filled with the file name? that possible? IE, John smith is the name of the file and it would auto fill that into all the name boxes on the sheets. And could i get the date to auto fill it self from the first date box? I included two files, one is a blank sheet and the other is a sample of the data entered and what i want it to look like.
I receive input for events from three regions (East, Central, West) and have to match those against a calendar. Multiple events can happen at the same time. I want to be able to read the input into an area under the calendar dates, create a merged cell with those start-end dates (inclusive), then input the region and event (concatenated) into the merged cell. It would look like this (with example of the input data below the calendar), but over the course of a year or more. There can be as many as twelve events occurring simultaneously.
Example.xlsx
Input from the regions can happen sporadically, so it is a "living" spreadsheet that can change dynamically.
I'm pretty good with formulas, but this seems like it would need a VBA solution - which I'm not good at!
I regularly build a pivot table using VBA. I now want to group countries into regions within the pivot table, and I want to automate that using vba.
For simplicity, assume I have the countries Germany, Netherlands, Belgium, Luxembourg, Norway and Sweden; and I want to create the Groups BeNeLux and Scandinavia. (In reality, I have 150 countries, one group of 10 countries, 2 countries I want to show individually, and the rest of the world I want to group together)
I know I could select the cells using something like cells(d2:f2).group, but that seems wrong....
I have attached a file showing how far I can get with vba and where i want to get.
I have also tried things like ".DataRange.cells(2).group by:=3" but that never led to anything....
grouping reqs.xls
why I'm getting an error with this code. I have a data set that becomes larger each time I run the report, so by selecting all the cells and naming the range, I don't have to hard-code the cells involved. Then, I want to be able to create a table from that named range. The error comes on the last line of code.
VB:
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Name = "TestRange"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("TestRange"), , xlYes).Name = "Table2"
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
View 7 Replies View RelatedCan record the all those actions which we need to do on a regular basis. In my work i have to create the pivot table to a data which gets changed every day but the data sequence remains the same.
I have tried to record the macro for the pivot table but when i tried to run the macro again then the below error message comes.
"run time error 5" "Invalid procedure call or argument" when i click on debug then the below macro program is highlighted in yellow color.
For example...
I have data for monday.. Tueday ..Wednesday as individual excel
All the excel have same column names and dynamic values.
Is there a way i can create one pivot with different excel as source
I am trying to do is create a list from a large table (see attached). I have 2 columns (A and B) listing 'Asset' and 'Area' and then 2 rows (1 and 2) listing 'type of task' and 'task'. This forms a table with entries in the cells (from C3 onwards) denoting frequency of each task at each location, i.e. daily, monthly, 6 monthly and annual. This gives me quite a large table which is quite confusing. Can I create a formula to sort all the daily activities which will give me a list of the 4 corresponding properties 'country', 'location' (in columns A and B) and 'type of task', 'task' (in Rows 1 and 2)? Then do the same for the different frequencies populated in the table, 'monthly', '6 monthly' and 'annual' ???
View 7 Replies View RelatedI have a Column, say column A, setup with a lot of repeated data. Now what I want is to pick only a single value from each set of data and put them into a separate column. for example:
Col A
1
2
3
2
4
1
5
4
1s
a2
1s
a2
a2
6
What I want the excel to do is to pick from the above data only a unique value and put them into a separate col, like this:
Col C
1
2
3
4
5
1s
a2
6
So I have recorded a macro that filters for certain criteria in a master list of projects, then copies the projects that meets this criteria and pastes it into a timeline in another worksheet. However, because the list of projects gets bigger and smaller when the filter criteria changes, right now I have to create twenty extra rows so that the pasted list doesnt ourgrow my timeline. How I could input a few lines in my macro so that the table for the timeline expands as the number of prjects grows or shrinks.
One method I have tried is to copy the filtered data and "Insert Copied Cells" into the table, however I don't even get the option to insert copied cells after I have copied the data...