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.
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.
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 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.
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.
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 have a lot of data to try and summarize but they are in multiple smaller tables. I wish to only extract 2 variables, 'number' and 'total' from all of my smaller tables (lots of them) so the problem looks like this, i have these tables for example where the 'total' title is in a different column depending on which of the smaller tables you are looking at:
cat 1 cat 2 cat 3 total
[Code]....
I am trying to get:
number Total 123456 30 234567 60
[Code]...
Because the number column is fixed and all of the smaller tables will all have a number in the same column maybe i can insert a column next to the number column and say "find the value where the column title is 'total' - however the location of the column title would change so not sure if this would work? I was also thinking of sorting the columns but that will not work either because of the smaller multiple tables?
I have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?
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 a sheet in my workbook named Summary, from this I would like to run a macro that cycles through every sheet except "Summary" & "Variables" & "Cash" and puts the sheet name in E5:down and cell AH30 from every sheet in F5:down.
I know it's possible, but I'm not sure how to set up the loop.
I need a macro that will copy and paste the data from the range below from every file in a directory with each file in the directory on a new row. My goal is to build a database with the information in the files in the directory. I will need to be able to put the directory in the code at a later time as it has not be set up yet. it will be on the local computer and not on a network share.....
I have a spreadsheet which details an employee number, name, event type and no events as shown in attachment. (SHEET1). I would like to be able to create a summary sheet in the same workbook which will search through the list of usernames and produce a summary for each user.(SHEET2). The number of users varies in sheet1 so I would need to handle this dynamically.
I have a list of unsorted codes in A1:A30. There are a total of only 5 unique codes represented, but most of the codes occur several times.
I'd like to produce a summary list in A35:A40 of each of the 5 codes represented in the range above.
Is there a formula that can be copied and pasted into A35:A40 that will search A1:A30 and return one of each code, or can this only be done using a macro?
My brother created an invoice for his company using excel; it's s typical invoice:
Name, Date, Invoice# Total, Tax, etc. He would like to have all of the mentioned information transfer to a summary sheet as he has between 30 and 60 per quarter. The problem is that they are not in a workbook. So each invoice is it's own .xls file. This can be time consuming if he had to open and copy from each sheet.
Is there a way to copy the info to a summary sheet?
I have a very large table that I want to be able to summarize neatly and columns that have zeros for a specific person, so its easier to input data later.
I have 52 sheets in my workbook. Is there a simple way of creating a summary page of the data contained within specific cells which are the same reference on each sheet ?
The slow way is to edit the Sheet reference in the formula, but I sense this is not the most elegant way to do it.
I have three worksheets called North, East and South. Data is held in each of these sheets starting in D1:BZ144 and the data is in columns of three. Example columns D:E is one set of records, F:H is another and so on. The first cell of each set of columns is the company name.
I need to copy all of these records into a Summary works sheet so that the records are in held in columns B:D and that the first cell is shown shown in column 'A'.
Secondly, in a second sheet I'm trying to setup a lookup so that I can call the company name and the records from the sheets North, East and South are shown that relate. I've tried using HLOOKUP but as the company name only appears in the first column I cannot get columns rwo and three to show.
My boss requires me to prepare forms for vouchers that we release to our suppliers.
I encode the details manually to excel with his prescribed format. each voucher are encoded on separate sheets on one file.
Now he requires me to prepare a summary for all the voucher forms which is now becoming a pain since i now hold 50+ suppliers. and each day i encode about 120 vouchers.
I found through this forum that there is a way to automate tasks on excel like preparing a summary sheet.
I'm looking to create a summary sheet of all data consolidated to one tab.
On the attached workbook, I want to pull all of the data from the monthly tabs and consolidate it all to the 2012 Totals tab.
How to only pull the nonblank rows, and ignore the headers.
I would like to be able to avoid using a macro for this, if at all possible. I would like for the summary to update dynamically whenever new data is added to any of the monthly tabs.
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 am using Multiple worksheets ( 30 to 35 ) of same format to calculate Individual scores of an event. Each sheet contains Participant Name in C4 and Score in E 21.
Macro to provide a summary sheet which returns C4 & E21 in all the sheets..
The score in E21 is calculated thru another macro done with excel recording feature..
I am currently pulling information from a database into Excel. In column A is a list of dates that vary depending on how many days I decide to pull. On any given day there are options to which car will be driven and it is random and there are attributes associated with that drive such as below. If the car was not driven the data pull inputs "No Data" or a "0". On any given day up to three cars can be driven but it is still possible that only one will be driven. This is shown in the second table below. I'd like to be able to create an automated summary table such as the first table below. The summary table should be organized by date and show what cars were driven and their attributes. I know this seems simple but in reality I have six different cars and up to 10 attributes for each car and will be pulling months at a time. The only thought I have had is to do a for loop that checks every individual cell but I am not sure how to implement that.
it's possible to create new fields (variables/columns) on the fly (without adding them to the original data table).
For this example, assume I have a column for: job code estimated hours job code actual hours total estimated hours total actual hours
Excel has made it easy to run averages or sums for any of those columns, but what if I want to see what the average proportion for job code estimated hours/ total estimated hours? Is there an easy way to do it with pivot tables without adding a column to my database? I'm working in Excel 2007, my data table size is approximately 100 columns by 200 rows.
I created a macro by using the macro recorder. It creates some columns and formulas and does some formatting, then it is to take the data and create some pivot tables.
for some reason it gets stuck on the first pivot table. It creates it, but it wont add the first field to the table.
I have recorded a Macro to create Pivot Tables, I would like this to run in any workbook. The problem is that every time a Pivot Table is generated Excel alters the number of the table(Pivot Table options). This then does not match that in the Macro as below("PivotTable1").
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R37C6").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("GAME") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("LANGUAGE") .Orientation = xlRowField .Position = 2
ive got 1 sheet,with 2 printable pages in it. 1 being the summery page and 1 being the inventory page.
what i would like to be able to do is. on the summery bit ive got a code in A22 and a number in G22 to save me time i need to create a list in the inventory bit starting at A62
im hoping it will look somthing like this
A62 = what ever the code is ( JR269 ) in A22 A63 = same code and so on until i have 26 in a list, 26 being G22
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.
