I want to keep the raw data on one sheet, and have a graphical representation of that data on a second sheet. On the second sheet I would like the user to be able to filter the data on the first sheet which will update the graph as filters are applied.
Before I start trying to code something in VBA, is there a way to display the filter at the top of the data on the first sheet on my second sheet? So I would have an exact replica of the filter cell on the first sheet functioning exactly the same way, but on the second sheet?
Data manipulation question here: how to dynamically filter and sort a multi-column data set? My end goal is to be able to (1) quickly collapse all columns into one single column, (2) remove all duplicates, and (3) sort the information in ascending order. A reference sheet is attached in case it's useful.
IS there a way to filter a dataset using the criteria in a hidden column? Auto-Filter will not allow this, and Advanced Filter is not user-friendly for the end-user of this file.
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
I have a spreadsheet with two sheets. One with car sales on and 1 with cars that have been sold on.
In the sheet that contains cars if a car has been sold i type sold in the price field and move it to the sold sheet. This is done by cut and paste. This is very time consuming as you can imagine.
Is there a way i can create some sort of if function that when i click a button searches for all records that have the word sold in it and moves it to the correct sheet?
I was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.
I need to update a sheet called Database by filtering the word Served in column F (6th field) of another sheet called Detention Register. After the 'Served' rows have been filtered today's date needs to be pasted into column E for all of these rows.
The filtered entries from A:F only then need to be cut & pasted into the next empty A column cell in the Database sheet. Finally, another macro called Update Database needs to be called.
I have 4 coloumns: Barcode, Date, Qty, Intials. The products will be scanned and the above cells will be populated via a VBA code. What the problem is that in the Barcode column - a product may be scanned on several different days, each entry may have a different Qty value. So how can I autofilter to show the various barcodes and get the total value of Qty (subtotal) and then transfer this value into a new sheet showing the relevant barcode and subtotal Qty?
Business DeveloperDonna HoffmanCount of Activity CountDate Client NameContact NameActivity Name3/5/20123/6/20123/7/2012Grand TotalxxxxxxxxxxxxxD'Agostino, Esq., Michael C.Email Sent (CLIENT)11xxxxxxxxxxxxxxxx11xxxxxxxxxxxxxxMartin, DavidEmail Sent (CLIENT)11xxxxxxxxxxxxxxxxxx11xxxxxxxxxxxxxxxxxxYoder, MichaelEmail Sent
[Code] .......
I would like to filter by Business Developer and copy and paste all text to a new workbook. I would need to repeat the same code for all Business Developers and the number of columns will vary.
This is the code I have so far. I get an error msg when it tries to paste the data to the new workbook.
Code: Macro5 Macro ' Macro recorded 3/14/2012 by MPS Group ' ' ActiveSheet.PivotTables("PivotTable2").PivotFields("Business Developer"). _ CurrentPage = "Donna Hoffman" Columns("A:L").Select
I've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.
A B C D E
1 DATE TEAM PITCHERS RESULT
2 4/1 nyy Sabathia ???????
[Code] ........
So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E----- Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
Trying to add a vlookup to a data set and cannot remember how to do.....
I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......
In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.
I have names is Column A (about 200) and names in Column B (about 800) along with additional info in other columns. Column A was just added to an existing spreadsheet and will be deleted once I accomplish my task. The goal is to come up with the 200 names in column A along with the additional info in the other columns (address, phone) and to delete all of remaining names in B (about 600, 800-200) I'm trying to figure out the most efficient way to do this. I'm not sure if there is a function to match the text in Column A to Column B and at the same time move the row in Column A to match the row the name is located in Column B.
For example John Smith is currently in A1 and John Smith is in B7 and then John's phone number is in C7. I want the John Smith in A1 to move to A7. If I can make that happen for all 200 names I will then delete all rows with no entry in column A.
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
I have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.
I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.
