I don't know where to begin or if this is even possible. I have report that I need to format.
See example file. Note: real file has 2000 rows.
The book date and book amount in the orginal is the POS date and POS amount in the format sheet. I don't need the "over/short" data from the original. Col. A contains store #'s and they are 2,3,or 4 numbers long preceeded by an "S-". In the formatted sheet I need the "S-" removed. The data is grouped by column C. 3 of the same equals 1. Groups vary.
I pull a report every month which has the same name - at the beginning - (i.e. Monthly File Report_06012014.xls but the date is different every month; where we keep a copy of them every month.
So I need a vba to pull the data from that specific file - since I'll have more files open, but wouldn't keep the date since it will be different every month - is there an & that goes at the end or somehting: Monthly File Report &....xlsm?!
I have a complex data set that has been imported into Excel from a binary data file and I am looking for a way to simply pull out specific fields. I tried to record a Macro that would simply delete the rows between the data rows I want to keep, but it always references itself to those same ranges and I'm not sure how to identify that I want it to sequentially move down the page deleting the same number of rows, and adding the row of information I want to keep as it goes.
I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
In the annual table tab, I want to be able to select a sales person or other field, and create a table that will pull data from all the tabs. For example, if I select the salesperson Kelly, I want it to pull all of the data from January, February, and March, and compile it into a table.
I would want the table to be dynamic also - I want to be able to quickly sum/average all of that salesperson's sales for all months.
I have a pivot table created. How do I determine which sheet it is pulling the data from, hence the source of the pivot table? Also, if can you go into source and modify data points, then in pivot table, will the results would be automatically modified with updated data points?
I've been playing around with VLOOPUP, MATCH, SUMPRODUCT... But I can't seem to get this one right..
See the attached Excel file..
I think it should be fairly explanatory.. I want to use the dates in SUMMARY sheet, to pull out the data in the DATA TABLE sheet. The numbers in Column A on the DATA TABLE sheet, is first date in each month, and the numbers represent the day of the month.. .
I have a data table that contains begin dates, end dates, and colors. The data is input starting with the earliest begin date. On a second worksheet, I have a listing of the years starting with the year of the first begin date and ending with the end of this year. I need to be able to pull any color choices associated with a year from the data table on the first worksheet. Some years may not be specifically listed in the data sheet as the person could have had the same color choice for several years. Sometimes a person could have more than one color choice in a year.
I am at a loss for how to get to the data I need. I created an example spreadsheet to demonstrate the data I am working with, and an example of what I need my results to look like in the end.
I'm using Excel 2007 and I'm looking for a way to extract certain records/rows from a table and to have that data copied to another area of the spreadsheet. I actually want to extract Invoice amounts according to a specific account number so that I eventually end up with all the invoices for a particular client on another tab so that they can be printed out as a kind of client statement.
I know there are ways to filter and then copy the records/rows to another tab but I want it to be automated as far as possible. What I really want to do is to "pull" the records into a new tab by using some kind of formula in the cells where I want the data rather than having to copy it there. I know how to sum data for this kind of thing but what I'm really struggling on here is being able to "Display all the records in a seperate area" first before I eventually add formulas to sum.
I don't mind the records being coppied if the process is automated but don't really want to get into macros as writing macros isn't my strong point.
I've tried using "VLOOKUP" but it will only return values for the first data item it finds (Account number in my case), whereas I really need it to continue to return values for the data item it finds if there are multiple instances of it!
My specification is: Calculate how many people within the UK live in a given area. Within that area what are the different age distributions (0-5 years old, 6-10 years, etc.) Then display the data twice - 1/ show each age range and how many people in the given area fall into each one. 2/ As before only report on people in the given area, and display age range, but also display them by ID number (col E in example file).
Can someone help me build the pivot tables that performs my analysis? (I got it working with countifs but it was an inelegant longwinded kludge.)
I've attached an example (.xls zipped up) which shows the data on one tab and my failed pivot table attempt on another.
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).
I pulled a report into excel that lists staff details and workgroups that they have access to. There are nearly 8000 staff who can have anywhere from 0 to 120 workgroups.
The workgroups are listed with the staff details in the following format:
My workbook has several worksheets so do you need the name of those to be excluded? Or how many sheets i require which are located to the right of the summary tab?
Detail - 5 sheets to the left of summary tab are to be ingored, 7 to the right of the summary are to be pulled (let's say they're named Red, Green, Blue). I only want the colour named tab data pulled and pasted into the summary
Code is below :
Sub karryan() Dim i As Long For i = 1 To 3 Sheets(i).UsedRange.offset(1).Copy Sheets("Summary").Range("A" & Rows.count).End(3)(2) Next i End Sub
I have six images in "Sheet3" I want a macro to copy and paste image one if cell A3 = 0. How do I know the "name" of each picture so I can change the image based on the value?
I have a sheet with 200 sheets in called workbook x , I need to create a summary new workbook with only data on 1 sheet.
I want to pull specific cells from the large workbook x to 1 single sheet on a summary workbook:
A6 I want to be surname taken from B1 of workbook x B6 will be first name taken from C1 of workbook x Then I will copy data from M1 on mastersheet to cell C6 on new workbook Then I want to copy Row A11 up to T11 and paste on the new workbook (Summary) in D6 staying in the row 6 Not sure if this is the tricky bit I want to repeat as above B11 to T11 but paste in the next empty cell of the same row of D in my summary I need to repeat this action upto A23 to T23
Then I need it to move on to the next sheet in X and repeat all the steps above but do it on the next row down row 7 and so on
The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").
I also need it to look in column "C" in the ("FY09 SOF") file and pull the entire row if it finds the word "Payroll".
For some reason it will pull everything needed except the "Payroll" rows. What am I doing wrong?
Sub get_data() Dim wb As Workbook, wbDest As Workbook Dim ws As Worksheet, wsDest As Worksheet Dim lngCalc As Long Dim FoundCells As Range Dim FoundCell As Range
Set wb = Workbooks("FY09 SOF") Set wbDest = Workbooks("FY09 PR Log Blank") Set wsDest = wbDest.Worksheets("Paste all here, then sort")
With Application .ScreenUpdating = False..........................
I have an excel 2010 spreadsheet that lists all of our vendors and the amount we spent with them over the last year.
I want to know how many of them were local vendors.
I have a list of all the zip codes within a 100 mile radius (there are about 500 zip codes). I would like to write a macro or sort function that searches the entire vendor list and only reports back those vendors that are included in the zip codes I specify.
The columns are as follows:
A B C D E Vendor City StateZip CodeTotal AP Vendor 1TROY AL36082527.37 Vendor 2PHOENIXAZ85054100 Vendor 3TUCSONAZ8571416255
I want to keep the all the columns, I just want to eliminate all of the vendors that do not fall within the zip code criteria I set.
I need to pull a specific word from a string of text in a cell and have that word shown in an adjacant cell. For example A1 will contain the text "Smith Sun Alliance Pension Fund" I need B2 to show "Pension". I cannot use any filtering or text to columns as the word Pension can be anywhere within the text in A1 and I have thousands of entries. So I need a function.
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code: Sub Results2() ' Results2 Macro ' copy table filter power by greater than and less than Sheets("Finalizing Results 2").Select Cells.Select
i have a table with year and day in rows and months as columns. I'm looking for a formula that will look up a number in that table based on the date given in colP and put the number in Q. See example.
I need to copy/cut and paste an Html table from a website into and Excel sheet without the data being transposed. See link for the table I need to get into Excel:
I threw together some sample data, just to test things out. It's very basic, one table of "Customers" and another table with the 50 US State's and their corresponding abbreviations. In the "Customers" table, there is a column of state abbreviations, and in the "States" table there is a column of state abbreviations as well. I have a relationship set up between these two in PowerPivot.
Each "Customer" in the "Customers" table has a unique "User ID". In some states, there are multiple "Customers" (User ID's).
When I try to pull a PowerPivot Table off of these tables, it's showing me all the states as being associated with every user ID. It looks correct if I just pull in the "States" column and the "User ID" column from the "Customers" table...
image1.jpg
But as soon as I drag in the "Full State Name" column from the "States" table, it screws up the PowerPivot Table and shows all the state names being related to the state abbreviations, and all the User ID's as being related to all the states. (This isn't the full image of the table, only part of it, since the full image would be too large).
image2.jpg
I'm used to doing everything with VLOOKUP's, and seldom used PivotTables at all in the past. But it was my understanding that these new PowerPivots would eliminate much of the need for VLOOKUP's.
I have been having issues with some excel fields not showing up in my pivot table pull downs.
For example I entered the date 6/8/97 into a field in my database and after I hit refresh on my pivot table that specific date doesn't show up in the Date field list pull down. It is like the pivot table doesn't even see that date and/or it is recognizing it as another date. I have this same issue w/ other field lists (eg. last names).
