i have a pivot table an extract of each data contained in this table.
[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-lauralaura11
Commercial-laura Totalgh11
custody-jonathanjonathan112
k11
custody-jonathan Totalgh1113
settlement-ludovicludovic11
settlement-ludovic Totalgh11
SPQC-elodieelodie112
SPQC-elodie Totalgh112
Grand Total1337
I've got two columns with data. The first is text, and the second is numbers. So I want to be able to enter a certain criteria that would be contained in the text, and have excel return a list of the rows of text that contain that criteria and that have the highest 3 values in the corresponding column. I can use multiple cells to do this if need be (i.e. one cell for the text with the highest value, one for the second highest value, and a third for the next highest value).
View 4 Replies View RelatedI have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies View RelatedI have an excel template populated with information. But I cannot extract the data to run pivot table.
View 3 Replies View Related I have a pivot table, and I would like to be able to have a formula or vba to extract the data to separate sheets. The pivot table looks similar to;
Row Labels
Count of FileSemester
John Smith
8
[Code].....
The pivot table consists of employee name (John Smith), followed by class code and qty. I have a separate spreadsheet for each employee and what I need to be able to do is to extract the class & qty into these spreadsheets.
I need to extract data from a spreadsheet given to me by my client, the best way to describe what I want to do is "a reverse of a pivot table"
The source sheet is arranged as follows:
Column A: Employee name every 3rd row
Column B: Normal Time on row 2, time and a half on row 3, double time on
row 4, normal time on row 5, time and a half on row 6 etc etc
Column C to I inclusive: Number of hours worked (on that day)
Column J: Subtotal of the 7 days (columns C to I)
Column K to Column DZ: Repeats of Column C to J
Row 1: dates in columns C to DY (except for the weekly subtotals)
I have many spreadsheets like this that I would prefer to have the information in a simple list format with the fields: Name, Date, Rate
Class (Normal Time, Time and a Half etc), Hours. Is there a simple/quick way to extract this information into the above mentioned fields? Am i being stupid and missing a simple solution?
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies View RelatedI 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 ?
I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?
View 2 Replies View RelatedI need to manipulate some data in order to make it reasonable. I'm reading up on Macro's now in order to be able to do this stuff in the future, but I think macro's are a long term thing.
Actually before I go into that, is there any book one can recommend for macro's? I do have a friend's books, How to Do Everything with MS Office Excel 2003, MS Excel Bible 2003, and MS Office - Excel 2003 Formulas. Are these good or any other recommendations? In particular I would like some data to play around w/ so that when in the book they tel me to do something I actually have some data to manipulate. I imagine learning the basics of macro's will be easy enough, but as I understand it the real power of macro's comes from programming in VB, and I dont see how that can be covered in these books.
Now for the more specific questions -
My raw data looks like the attached excel file called Raw Data, I want it to end up as the other attached excel file called Result. How would I create a macro to read where the ticker begins and ends? Is there any way to do it w/o VB knowledge?
Raw Data:
[url]
Resulting Data:
[url]
More importantly, I want to be able to order the tickers by how many times they've been mentioned. For example in the second excel file called Result, Laura has been mentioned twice as often as anyone else. When I sort this list I want to sort by who's been mentioned the most. So I'd like to see 6 lines of her data, followed by the person mentioned the second most (if they had 5 lines, the next 5 should be them etc). For this one would pivot tables do the job, or do I need macro's for this as well?
I have come across a situation where I am listing the top 10 customers based on their sales. I have a worksheet for each product I sell. I'm wondering if there is a way to write an excel formula to pull just the top 10 customers from just a single pivot table (respective to the product worksheet) instead of having to create a new pivot for every product and sort based on top 10 and then link to my spreadsheets?
View 2 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 RelatedI have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
I've attached some dummy data. Basically, everyday I'm going to dump a report containing data into a 'Data Dump' tab. 'Pivot 1 - Filtered' I have set up so that it will show specific product IDs only (In this example I only want to see 1X, 2X and 9D).
However, I now have a need to see the total value of that order number in 'Pivot 2 - Autofilter'. (For Example, order number 1111 has both 1X and 8D contained within it). Now obviously the simple thing to do would be to filter every order number shown in pivot 1, and filter pivot 2 with them. However when I'm doing this for real, this can be tens/hundreds of order numbers.
The only workaround I have found is to put a column at the side using VLOOKUP and if ISERROR is false then include in pivot, then used an autofilter on that, but I'd like to keep the normal pivot if possible.
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View 2 Replies View RelatedI 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.
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
extracting data from a table.
team 01
team 02
team 03
[Code]....
I want to know what are the teams in which sam a member. Peoples' names should be selected from a dropdown menu and names of the teams should be displayed below the menu.
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
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 need to extract/copy (to another sheet) data of all 4 columns in a row for each first entry and last exit (for each person) from the attached file.
View 10 Replies View RelatedI have some data which is copied from our system in the format below, I then need to add only parts of this information to an existing spreadsheet on the network.
This is the information (there could be lots of these entries, but all need to be added to the spreadsheet)
Device ooo.1111111111111111111111555666663333 performance has deteriorated.
increased from average value of 2041 days to 44589 days.
warning
2/6/2014 2:38:18 AM
aaa-ab-cde00.efghijklmno.ddd.kk
Device ooo.11111111111111111111115556666633356kk performance has deteriorated.
increased from average value of 2041 days to 44589 days.
warning
2/6/2014 2:38:18 AM
*aaa-ab-cde00.efghijklmno.ddd.kk*
I then need part of the above data to be transferred to in Excel in the following column headers (see attachment for layout)
DateTimeHostDeviceLow ValueHigh Value
what is the quickest and easiest way of updating this spreadsheet..... I have to update the spreadsheet every morning and it is very time consuming .
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies View RelatedI want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
View 3 Replies View RelatedI want to extract data in table with this order C7:AA7
Sheet2 *ABCDEFGHIJKLMNOPQRSTUVWXYZAA1LetterNo
*************************2A5*************************3B15*************************4C3*************************5D2
*************************6***************************7**ABCDABCDABCABABBBBBBBBBBB
i want to extract the data from the table
Sheet2  ABCDE1Data    2ABSCar1Car3Car6Car13ADFCar2Car5Car3Car24ANDCar1Car1Car1Car35ADXCar6Car6Car5Car1
6Â Â Â Â Â 7Â Â Â Â Â 8Â Â Â Â Â 9Results10Â Â Â Car1Data11Â Â Â Â ABS12Â Â Â Â ABS13Â Â Â Â AND14Â Â Â Â AND15Â Â Â Â AND16Â Â Â Â ADX17Â Â Â Â Â 18Â Â Â Â Data19Â Â Â
Car2ADF20Â Â Â Â ADF21Â Â Â Â Â 22Â Â Â Â Data23Â Â Â Car3ABS24Â Â Â Â ADF25Â Â Â Â AND26Â Â Â Â Â 27Â Â Â Â Data28Â Â Â Car5ADF29Â Â Â Â ADX
I manage a team that quote for business. They are targetted on number of quotes per month but then also wins per month, however a win may come a number of months after the original quote was generated.
They work off a spreadsheet where they log:
Quote date (And month)
Quote Value
... customer info etc
Win month (against orginal quote information)
Win value (against orginal quote information)
I started writing my pivot using the Quote month as the main reference point, IE Quote Month in the Row, and then put all the data in the central drop data section... However, there are two immediate problems with this:
They are targetted on quotes generated per month, some business won has come from last year, which they are lifting from the old spreadsheet and puttin in my new one... meaning that quotes they generated in Oct 2012 are now showing up as quotes for October this year.
If the wins span a number of months they are showing in January (if quoted in January) and not in March (when actually won)
Is there a better way of writing my pivot table??? There must be... ideally what I would like is half the table referencing the Quote date and half referencing the order date... but I don't know how to do this? I could probably do this with pages but I would like to show all on one sheet...
how can i sum pivot table filtering some values in other table. if i change filter, sum is changed
View 1 Replies View RelatedI want for my set of data. The attached .xls is pretty straight forward: the first column is a list of people (identified by their customer number) and the second column is the URL they visited.
Since many people went to multiple pages, there are dupes between the two columns, but all of the rows are unique. What i am looking for is a table that shows as a header the URL (just one) and then the list of people that went to that URL under the header. So it's really just one column of information. It seems like a perfect task for a pivot table.