Code To Filter, Sum And Return A Value From Data Set
Nov 26, 2008
I deal with large amounts of data. For a score card I need find a way to filter data based on two constraints, sum it and display the output in a table. The two constraints are: Business Unit and Maintenance Type. I played around with recording filters, but the code becomes very unwieldy. From past experience with this site, where I used 10 lines, there's a way to do it in 2. take a look at the attached example book, a truncated data set and the output.
I do not know VB coding but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.
The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually,
A1 2 3 100A A1 2 3 100B A1 2 3 100C A1 2 3 100D
A1 100A AA BA A1 100B AB BB A1 100C AC BC A1 100D AD BD
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data. DATA is the name of the raw data worksheet The criteria range should be AZ1:BC3, but of course royally breaks it...
I have a folder on a public drive. (\CFDIVIX1PublicPurchasing2009)
This folder contains multiple sub-folders (the sub folders are vendors which I purchase merchandise from) each sub-folder has 1 .xls file in it. Each file has 1 sheet containing all the information I need to retrieve.
Each one of these files has the same header information in cells A1 through I2 so I manually place that when I create this new workbook prior to inserting the macro. Each work sheet has a different # of rows depending on the number of items I buy from that particular vendor but I feel safe saying it never exceeds 250 rows per worksheet.
What I need For the macro to pull up the first work sheet and pull out the data from cells A3 through I250 and place that data into my new work sheet in the same format. Then I want the code to move on to the next worksheet and pull out the A3 through I250 and place that data in the new worksheet directly below the last row of data that was pulled in. (Also it would be great if the code ignored blank values. Like if the first work sheet it pulled data from only had data in the first 10 rows I don’t want 240 rows of blank rows before the next set of data)
I hope that running this macro will accomplish transferring all of my data from these multiple sheets into a single work sheet containing all of the pricing for all of my vendors. Once I have this I can place a second sheet on the workbook and use a formula to pull out pricing information that I need on a per basis without having to search through 50 vendors to find who it comes from. I don’t control the vendor work sheets that I want to extract data from so I need to use the macro to run it weekly incase my purchasing dept makes changes.
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:?
>I think my issue is best displayed with an example: > >Here's my rows of data: > >Column A >Bob >Dave >Dave >Eric >Simon >Simon >Simon >Simon >Tom >etc.... ....
I'll assume this is in Sheet1!A1:A10000.
>Is there a formula that can return the following: >Bob >Dave >Eric >Simon >Tom >etc... ....
i have a work book of 12 sheets, the two main sheets 1+2 have 5000+ rows. by 30 columns. each row column b is a site number in ascending order(up to 4 for each site) so auto filter select = to site 5 about 10 seconds later i get the result of 3 sites try site 846 30 odd seconds later up pops the result. so i copied the sheet to a new work book and tried and it's nigh on instantaneous. next step copied sheet paste special values into new sheet in same original workbook and tried filter on that still slow as ever, thing is this book around 8meg and i cant for the life of me work out why ive cleared excessive formats and reset last cell on each sheet. there is a vlookup from sheet 1 to 2 for each site to return serial numbers. i have tried it in vba by turning autocal off/on but still no difference.
I have a worksheet which shows multiple tests and whether they are completed or not[code]Test 1 Person1 Test 2 Person2 Test 3...Person12. I want to search for a specific test, e.g. pH which could be in any of the "Test" columns but only return it if it is not completed. The "Person" columns will be blank if no one completed the test.
I set up the Advanced Filter criteria with all the appropriately labeled columns and for the Person columns, I put ="" but it is returning all rows which have pH in them whether they are completed or not. What should I be putting in the Person columns of the criteria to only return those rows which have the pH test but it is not completed. I attached an example of the criteria range and the filter window to help clarify (the full spreadsheet is huge)
I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.
Example: B K 3050 SH 1/1 Dining Room 3050 SH 1/1 Kitchen 2030 Fixed Foyer 2030 Fixed Living Room
In the Same sheet Starting on B:45 I want a list of only Unique Window Types:
B:45 and Down: 3050 SH 1/1 2030 Fixed
I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.
I have tried and tried to get the VBA code working that will tell me the number of visible rows in an autofiltered set of data, but the result I seem to be getting is always "1". Below is the most simple form of the code that I am using (it is based on previous posts and tutorials on this site). (I have also attached a workbook with sample data and the code)
With ActiveSheet Set rnData = .UsedRange With rnData . AutoFilter Field:=1, Criteria1:="5" .Select 'demonstrate that the rnData range is valid lcount = .SpecialCells(xlCellTypeVisible).Rows.Count End With End With
I got a job in hand and do not know how to solve. 1.º Part of the problem
I have a table that is exported to excel from a program, and this table is always different sizes so I really need a VBA code.
In column F i need to have the difference between the values in column G and column C
Ex: G1 - C1 = Goes to M1 G2 - C2 = Goes to M2 and so on
2.º Part of the problem
With the values in column M and column L. I need the sum of all column values in Column M where L is the value of 2. These data are to Sheet2 - cell A1
I have an Excel sheet with a few thousand rows which I would like to filter by a column with the name TrackingID. This column contains values like:
12AA1 23452BA2 234AA1 345635CA2 ...
I would like to filter this column by the third character from the end (which is always a letter from the range [A-Z]). In the example above, this would be:
I'm trying to automate advanced filter but can't get past having just 1 critreria. I have attached an example spreadsheet where the download dump is on "Data" sheet and the result of the advanced filter is on the "Result" page. I would like, if possible, for the result to come up every time the criteria is put in (they are all data validation lists).
so for example, if i select account code, all those codes come up then select period and the list dwindles down and again for cost centre. Is it possible to do this without using macros. I have found the following code butnot sure what to do with it (sorry).
I have a macro I am using that uses several date filters. There are filters for the previous month, previous 6 months, previous year, all dates in the future, etc.
Every month I have to edit the macro and change the dates in the macro so it remains accurate. Is there any way I can modify the macro so that the dates are stored in a cell within the workbook and not in the macro?
What I would like is to type the date ranges in a sheet within the workbook and have the macro look to those cells for the date filter instead of typing in exact date ranges within the macro. This way the actual macro code does not need to be modified every month. I would like to only have to change cells within the worksheet and not touch the macro code.
I have a workbook and have created a filter across range A1 - BU1. I want to filter column BQ. In this column I want to use the filter to show all values except for 0.
I have just started trying to learn VBA as its pretty cool but I am getting stuck on how to filter by non-blanks. It's probably something easy:- here is what i have got so far, but its not working
With ActiveSheet .AutoFilterMode = False . Range("A1:V1").AutoFilter .Range("A1:V1").AutoFilter Field:=22, Criteria1:=0 .Range("A1:V1").AutoFilter Field:=5, Criteria2:="<>" End With
I have a set of data that I need to sort and filter and create pivot tables with 2-3 times per week. Example data sheet attached (confidential data removed). I will insert the code that I am using for reference. The issue is that when the data is filtered for 'Approved Timesheets' I need the filter to be based on 2 criteria; If myBaseRow.Cells.Item(1, 21) <> "Approved" Then If myBaseRow.Cells.Item(1, 44) <=StartDate And myBaseRow.Cells.Item(1, 44) >=EndDate Then
The first If statement works fine, but the second appears to be ignored?? I have tried to colour this part of the code blue to make it easily visible.
Private Function PTSubtotals(ByRef PTField As PivotField)
PTField.Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) End Function Sub Timesheets() ' ' Timesheets Macro ' Timesheet Filter 4/22/2008 by Richard Francis ' Dim myWorkBook As Workbook Dim myBaseWorkSheet As Worksheet Dim myBaseRange As Range Dim myBaseRow As Range Dim RowsCounter As Long Dim StartDate As Date Dim EndDate As Date StartDate = Format(Date, "mm/dd/yyyy") EndDate = Format(Date, "mm/dd/yyyy")
On Error Resume Next StartDate = Application.InputBox("Enter start date", Type:=2) On Error Goto 0 If StartDate > 0 Then.................................
This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).
What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.
What I have is a workbook where I have to filter on a column called "Code" in a worksheet called "Current" and search for varies criteria and then copy these filerted rows and paste into a worksheet called "Past" and then delete the orginal rows form the "Current" tab.
e.g. filter on a column called "Code" and filter on the criteria that match "Monday", "123" and "Customer Accepted" and paste at the bottom of the "Past" tab and then delete from the "Current" tab. A message should then be displayed saying "Data Transferred OK" with the curser then highlighting "A1" on the "Current" tab.
The only cavat is that the code may find none, some or all of the required criteria, however, in the event of finding no matching data then a message should then be displayed saying "No Data Found" with the cursor then highlighting "A1" on the "Current" tab
As I have to repeat this to filter on various columns for various criteria I intend to modify the code accordingly, however, in once instance I will have to filter on a column called "Code" and filter on the criteria 1,2 and 3 etc but then I have to do second filter on column called "Code Extra" and then do a filter on the criteria 4, 5 and 6 so the additional code would also be good.
I have a list of customers in listbox1 (the text values of which come from a spreadsheet), that I want to refine as I type in textbox1. I'm not sure if it's possilbe, but I would like it to work similar to the itunes search (if you're familiar with it) where it searchs for any occurance of the text within the list as opposed to just searching for the letters at the beginning of the word.
In an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
Rather then this showing all values between these dates, I was hoping to see only those two dates. This will enable my to compare weekdays for a Dashboard.
I have a large worksheet in drive C: called 'hits.csv' that has manufacturer part numbers in column G and column L.
I need macro code to find all rows in the worksheet where the part number in each cell in column G is exactly the same as the part number on the same row in the corresponding cell in column L. The macro should then delete/remove all rows where there is no exact match.
Note: The part number in every cell in column G is the only thing that is in each cell in that column. However just to warn you that the part number in every cell in column L is at the beginning of each cell, followed by a space and then a description of the part.
Once it has done the above, I would also like this macro code to open a worksheet called 'partnumbers.csv' in drive C:. It should then compare the part numbers in column A of this file with the part numbers in the above worksheet ('hits.csv'). The cells in Column D of the file 'partnumbers.csv' all contains a price. Where the macro finds an exact match on the part numbers in these 2 files, it should copy the price from the cell in column D in the file 'partnumbers.csv', into Column K of the other file, 'hits.csv' where the match exists.
the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?
Sub myfilt() Application. ScreenUpdating = False If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If Application.CutCopyMode = False Application.ScreenUpdating = True
I've taken data from a fixed-length text file and the records are identified using the first two characters on each line of the text file. I've managed to identify these in each row with other bits of code so that was fine.
Now, I have 56 string variables (identifying my record types), which I'm assigning into an array. Then, I want to go through each variable in the array, performing the same action by way of a For Next Loop where, by taking this particular RecIDNo (see my code) string variable I will get the code to copy/paste the data into the relevant worksheet (where later on I will apply a text to columns using another array). But first, this set must work before I can do that! To avoid confusion, note that the record types i.e. 01 through to 56, will be filtered, then copied into the corresponding worksheet with the same number.
The problem I'm getting is the autofilter I'm applying, then the array I'm trying to reference (to avoid repetitive coding!) doesn't work as the code doesn't compile (I've not worked a lot with Arrays but I'm learning all the time!)
Option Base 1 Sub test() 'The array is declared here: Dim RecIDNo(56) As String 'The arrays are assigned the string variables below: RecIDNo(1) = "01" .... RecIDNo(56) = "56" 'This worksheet contains my data: Sheets("DATA").Select
For RecIDNo(1 To 56) '<--I THINK THIS IS WHERE I'M GOING WRONG!! 'Filter and select arrays:................
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm‎
I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. I've attached an excel file as an example.
