i've got a table that i'd like to use as the source to a new table derived from it, by pulling certain data depending on the value in a given column. for example, if the data in column 3 of a given row has a '1', i want that row to be included in the derived table, and not included if not.
derived table
1 apples
3 pears
4 cherries
7 peaches
some info about the situation, and rules to use-
- i don't know beforehand what the data in the source table are
- data in the source table will change from week to week, so i have to use a general solution
- i know that there's a maximum table size for both the source and derived tables [600 rows and 300 rows, respectively]
How to do this just by using excel functions, w/o resorting to vba? i've tried various permutations of formula expressions using MATCH(), INDEX(), CELL(), and ADDRESS(), but i keep getting #REF! and #VALUE! errors and how to proceed.
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 received a database containing listings of products and their sales, by month (see left side of attached file). What I actually need is to have this table converted (transposed) into a “flat database” which I can later manipulate with a pivot table (see desired output on the right side of the attached file). In the attached file I have drafted the desired output format of 1 line from the raw table, which I would like to get.
What I am looking for (if I might be so bold and ask) is a macro that will allow me to have a simmilar result for ALL the lines (there can quite a few). I think this output format can be achieved with a few simple loops, probably nested, but unfortunately I was not able to find the right sequence (being a novice in VBA). I have tried to find the answer here, between all the posts regarding “transpose” but couldn’t find something that looked (to me) as containing a relevant solution.
I have four tables out of which three are similar table and one table has one different column, but i wanted to have summary of all four table in one table.
I have a list of dates in Column A, with a list of associated values in Column B. I'd like to create a small summary table that will give a count of the dates in Column A and a sum of the values in Column B, broken into a bucket for each day of the week.
I know how to create this table using a short macro that would loop through the list of dates, but if possible I'd like to calculate the values using worksheet formulas so that I don't have to run the macro each time another set of data is added to the list.
I have a report of 1200 plans (approx 2000 rows). Attached is just a small example of data that comes from the report. There are 3 plans in column B (Plan X, Y, Z). I want one row of data for each plan with the plan roles and Employee ID filling those roles listed horizontally instead of vertically. It is only the roles and Employee IDs filling the roles that cause the data to be more than one row. In this example, I’d want only 3 rows of data (one for each plan). I want to include all data shown for each plan but want it on one row per plan. Possible roles are Plan Owner, Plan Steward (there can be up to 10 stewards per plan), Plan SPOC, BU SPOC, Compliance Director, and Officer.
I use a specific report on a daily basis which is attached here. Is it possible that that report is generated automatically in the same sequence of rows once the data is updated in another sheet. I am not good in VBA so looking some built in function. I have tried the pivot but all the formatting and design of the report is changed. use the Template wizard with data tracking is used is the above exercise is possible. Is there any other better methord is available in excel. Is there any good web site availble to learn these things.
I inherited a spreadsheet with a table which summarizes counts based on values in columns from another sheet. For example:
BrownWhiteMulti Dog326 Cat65 Fish479
The numbers are derived from the second sheet, which displays 1 in Col P for every value of Dog in Col B, 1 in Col Q for every value of Brown in Col C, etc. Col A contains the names of animals. So, in the above matrix the formula for Brown Dog is sum of all 1s in Col P + 1s in Col Q. In another sheet, I would have like Brown Dog in row 1 with all the names of brown dogs going across the column, Brown Cat in row 2 with the names next to it, showing the details behind each number.
I have 2 worksheets which have a table on each. I would like to create a list using data from both tables. The list I am looking to create is for an upload fom an excel planning sheet into an ERP system and requires certain data fields and a certain format. Both table have the same amount of fields and the dates across the top. On worksheet one there are material numbers on the left and dates across the top. In the list I require I would need the dates down the left in the first column, next the material number, next the quantity from table one and then the last column I would require the value from the second worksheet which is relevant to the material number and date from the first table. I have tried pivot tables but cannot get this to work and feel it may work with a lookup. I have attached an example of the data.
1) RAW DATA tab has data for four fund managers. The number of managers will range from 0-200 in the final model. 2) A negative exposure number is a short, and a positive is a long 3) The "Return" is from Bloomberg downloaded data in the live model but I have made up numbers for the purposes of illustration. 4) The Summary tab shows what I would like my final output to be. Please note that (1) the number of managers, and the number of stocks per manager is dynamic (2) the number of longs and shorts is also dynamic (3) i want the impact total for longs (0.39%) to be calculated for me using the code (unless it is easier using another method)
The Summary sheet would have Manager 1, Manager 2 and so on but i have only included Manager 1 for purposes of illustration.
You might say it is easier to use a pivot table, but it is hard to get this format with longs and shorts separated. I would also like errors to be ignored when summing totals in the Summary sheet - these errors occur sometimes on Bloomberg when a company's name changes or they get taken over.
This sheet is due to run by itself when i am not around. (I can automate it myself once I know how to get my desired table).
I have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
I would like to summarize information from multiple tables (across multiple worksheets) into one table using a formula.
I have Attached an example: Book6.xlsx‎
The 'option plan 1' tab shows multiple summary tables for different types of clothing. (the type of clothing is text in the top left hand side of each table just under the month i.e. "jersey tops"). This tab is an example of 6 similar tabs that come from 6 different workbooks...
For each clothing type (i.e., Jersey Tops) I would like to extract the TTL OPTIONS no. and the TTL UNITS no. for each store and show these on the table in the "front Sheet". against the clothing type.
Is there a formula that can match the Clothing type from column A in Option plan 1 tab with Column C in the the front sheet tab and subsequently Match up the TTl Units and Options for each store on the front sheet tab.
I have manually keyed in what I would like the formula to show for 'jersey tops' on the Front sheet tab.
Code: Date Jan-14..........Jun-14............Dec-14...........Jan-15..........Jun-15............Dec-15 Measure1 Measure2 Measure3
Code: Date 2014 2015 2016 Measure1 Measure2 Measure3
I have two tables, examples above In the first table, Jan-14 is a dropdown value that updates all the values to the right by a month increment when a specific date is selected
I then have some code that populates the cells which have 2014,2015,2016 in them.
What I need to do next is populate the 2nd table with values based on the date ranges in the above table.
I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.
An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.
The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.
The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.
Attached is five months of dummy sales data for eight products. My objective is to filter this data with date ranges (using Column A in Data sheet), and have the corresponding pie slices (shown in Pie Chart Sheet) be the summation of the Products grouped by their designated letter. Currently, the result I am getting is 6 pie slices for "Product A" and this is wrong. I want to see "Product A" show up only once in the LEGEND and only as one aggregated pie slice. The same holds true for the rest of the Product Letters.
I want to avoid using Pivot Tables. My objective is to keep my layout clean and simple. I would like to know if anyone out there could help me figure out a formula that will automatically catagorize all of the resulting filtered data by the Product Letter they share. Once the formula categorizes or groups together all of the resulting filtered data by Product Letter, I would like the formula to calculate a summation for each Product Letter category. After the formula finishes the summation task I would like the formula to feed these results to a Pie Chart. The resulting chart I would like to see will show individual pie slices for each Product Letter resulting from the date range filtering operation performed on Column A. The resulting letters can be anywhere from "A" to "H". My goal is to only have one individual pie slice representing each of the Product Letters resulting from a Date range filter. I thought such a task would be as easy as baking a pie, but it's turned out to be beyond my Excel skills.
I'm going to try to explain this as accurately as possible. This is my first post, and I'm seriously struggling with VBA, but i'm confident that someone out ther will have done this before.
I have a single workbook which has a single datasheet, and further sheets representing different months of the year.
Sheet 1 ("Data") contains the following columns and data:
DateStart MileageEnd MileageBusiness TripBusiness Mileage 01/05/2008013 get 13 02/05/200813260 03/05/200826100 euro 74 04/05/2008100113 adf 13 05/05/20081131130 06/05/20081131130 07/05/20081131238 alex begg1125 08/05/2008123812510 09/05/200812511288 service 37 10/05/2008128812980 11/05/2008129813110 12/05/2008131113780 13/05/2008137814000 14/05/2008140014560 15/05/200814561490.10
I then have a series of Summary sheets which are supposed to summarise the data on this single data sheetl. But this is where I have issues.
Basically, from the list of data above (running from 1st may 2008 - 31st December 2008), I want to pull out, per month all of the trips in that month. So for May (shown above) I need the date, business trip name, and the business mileage where the business trip name <> "".
To compound the issue, the columns that these are moving into are columns 1, 2 and 6 in the summary spreadsheet.
I have used some example code from cpearsons website to give me a list of nonblank cells in the Business Trip column, and this has worked, but I can't pull the other two columns out effectively.
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date ------------------------------------------------------------- John , 100 , $150 , 2011-04-01 17:07:50.0 John , 100 , $250 , 2011-04-01 17:07:50.0 Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase ----------------------------------------------------------- John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.
Develop a macro 1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.
2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):
The number of customers is dynamic i.e.it keeps changing every month
4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.
5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H
6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
I need a formula to automatically change the summary column according to the month we are in.
Ie: Last Months Data Nov Dec Jan Feb
1 8 1
7 4 7
9 2 9
'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?
I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))
I have a unit price and a quantity. I want to be able to take the sum of the extended price without having to add a column for extended price. I don't want to just hide it, either.
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100. I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest) The problem is i will get "....A file name already exist...do you want to overwrite.." prompt. Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
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.
I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
