Macro To Filter Out Data For Five Regions
Jun 24, 2006
I've a workbook in which I run an excel macro to filter out data for five regions W1, W2, W3, W4, W5, the macro creates five sheets (w1, w2, w3, w4, w5) and places them in the same workbook along with the original master sheet called "west".
The data in all the sheets is layed out in an identical fashion except that the the number of rows in each sheet will differ depending on the number of records for each region in the master sheet "west".
I was looking for some vba coding to automatically add sum totals in two columns (I & J) for all the five region worksheets.
The first record always begins from row 4, in all the sheets.
so as an example W1 sheet could have a sum formula in I20 = Sum (I4:I8)
and in COl J as Sum(J4:J18). Row 19 is a blank row, and the intention is to leave a blank row just before placing the sum total in all the sheets.
W2 will have the same starting range but might differ in how many rows to sum .
And so on for the 4 remaining region worksheets in the workbook.
View 9 Replies
ADVERTISEMENT
Jun 27, 2013
I have a list of post codes two letter starts by region. e.g.
inner london:
EC
WC
SW
W
NW
E
SE
In addition I have several very long lists of postcodes which I can obviously pull out the first two letters from using the Left function.
However I am wondering what is the best way to filter the column of postcodes into the postcode defined regions such as inner london nicely.
View 9 Replies
View Related
Mar 2, 2010
I am trying to write a macro do the folowing
1. Vlookup Regions in Sheet2 (eg. AP-Asia/Pasific)
2. Check which row they are in
3. then take the average of next 6 cells (AVERAGE 1,310, 6,744 etc..)
View 9 Replies
View Related
Jun 26, 2007
I have a database where columns may be added and deleted quite frequently and I want to automatically name each column by it's heading, and make sure those cells keep their name if a new column is added.
View 9 Replies
View Related
Mar 30, 2008
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
View 3 Replies
View Related
Jun 19, 2014
I have a spreadsheet. This has columns begining with A to S (with data in it)
It has rows beginning on row 5 and goes down to 85 but it gets bigger every day.
I want to create a macro with several phases
Phase 1 - Select the rows I specify, ie pop up box to decide on the rows
Phase 2 - Filter down and select the rows in this selection where column K has a value (unselecting the rest)
Phase 3 - Copying Columns A, B, D, E, F, K, from the selection
Phase 4 - Pasting these columns into A, B, C, D, E, F, of the sheet the macro sits on....
View 14 Replies
View Related
Feb 28, 2014
I have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.
This may not be an actual vba issue?
The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.
View 3 Replies
View Related
Jul 8, 2009
I'm having with creating a macro to filter some data from one sheet to another. I used the "record a macro" function to get the code I've posted below but part of the code is always changing.
In my workbook I have a main sheet called "Data" and another sheet called "Parts". The code below does what I need it to do, but the problem is that the cell range (A2:I139) is not constant and may change depending on how many rows of information I import on the "Data" sheet.
What I'd like to have happen is either have a dialog box pop up and prompt me to select the cell range (I've tried using the Application.InputBox function but can't figure it out), or since the first cell will always be A2 and the last column will always be I, have excel automatically figure out the last row that contains data.
View 2 Replies
View Related
Aug 16, 2008
I would like to use a dropdown box for all the names in my worksheet. Once a name is selected have all the rows filtered on that name and copied to a new workbook.
I think I'm making this harder then it is, but I'm just not comprehending what needs to be done.
View 9 Replies
View Related
Sep 9, 2006
I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.
View 9 Replies
View Related
Jul 31, 2008
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).
I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
View 9 Replies
View Related
Nov 19, 2013
I want to insert a cell reference into the code below to replace the hard coded "100500" so the value in cell A1 replaces the criteria below. Is this possible ?
Rows("3:3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$B$17").AutoFilter Field:=2, Criteria1:="100500"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
View 2 Replies
View Related
Dec 13, 2013
What I want to do is pull data from columns A,B,C,D in 3 other closed workbooks (export.csv, export1.csv, & export2.csv) to my main workbook (Thunder.xlsm)and append that data in columns A-D on my main workbook. In addition I created a list of 10 names (but need to expand or delete from this list as personnel change) in Column O that I would like to filter the data in A-D. The names in the list must find the data I want to keep in column D, the rest I want to delete (not the entire row, just A-D for that particular row that does not match the criteria in column O, and shift those cells up).
I've attached a sample spreadsheet that will clarify what I am asking. I'm preferably asking for 2 macro's, one for the pull of data, and the other for the filtering.
Column A
Column B
Column C
Column D
[Code]....
View 1 Replies
View Related
May 25, 2012
Writing code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
View 1 Replies
View Related
Oct 5, 2013
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:?
View 2 Replies
View Related
Jan 14, 2010
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
View 9 Replies
View Related
Mar 15, 2007
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
View 5 Replies
View Related
May 14, 2014
I'm trying to get the data from Receipt log (sheet 1) to automatically populate into the Print Receipt (sheet 2) and to automatically filter and go to the Activity by account (sheet 3). I am so new to this and totally lost.
See attached sample : Student fees.xlsm
View 12 Replies
View Related
Aug 17, 2009
I would like to know how to use the Data filter option "contains" in a macro? I've data of over 250 rows, would like to filter on column "ORF" conatins Aftermarket or AM-2. i tried to record the macro it works but when i use the same in if statements im not getting the desired result.
View 10 Replies
View Related
Apr 30, 2013
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.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
View 8 Replies
View Related
Mar 7, 2008
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.)
View 2 Replies
View Related
Dec 20, 2012
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?
View 3 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
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.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
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.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
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
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Aug 6, 2013
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.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
View 3 Replies
View Related
Feb 25, 2009
I need is to run a macro which does the following:
Autofilter is on, so on column AR filter to results that show '1', then on column H for all results there are 6 max but some dont show when '1' is selected as data may not be present.
So once I have filtered down AR and then filtered down H, I need a sum on AN, and AO for JUST those results!
Now I can do all this manually and have a quick mock code but this needs to be done sooner rather than later so im going to cont doing it manually if anyone can help with the code it will speed things up for me tho, thanks!
Sub SpeedUp()
' Location is in H, Quartile is in AR
' I to AM is irrelevant
' SUM of AN
' SUM of AO
Dim Arr As Variant
Arr = Array("F1", "F2", "F3", "F4")
For i = 1 To 4
So basically loop thru, loop thru, change the filtering, subtotal on the new filters copy to a new sheet, or even hold all values in a table or array till the end and i will manually paste
View 9 Replies
View Related
Jun 8, 2009
I'm trying to write a macro that will custom filter a column for cells containing the value in a cell, the macro I have so far is:
Sub Filter()
ActiveSheet.AutoFilterMode = off
LookupVal = Range("C3")
Range("A8").AutoFilter Field:=4, Criterial:=LookupVal
End Sub
However that only returns cells that are equal to C3, normally to do contains I would put "* *" around the value but then that removes the reference to cell C3.
View 2 Replies
View Related
Mar 24, 2007
Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).
Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False
Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?
View 9 Replies
View Related
Aug 6, 2008
I'm tryin to set up a macro to filter and sort and when I record the macro correctly it does not run properly.
I have detailed info below:
I created a sheet "Met Logbook" which contains all links to the source "Master Logbook". This master logbook has a lot of info added daily and we add info at the top of the sheet (we insert new rows every time at the top for different reasons). So what happens is the master logbook always changes its cells (when inserting new rows at the top, all the cells change row number). So I have just linked all the cells and more that we may be using in the Met logbook. This is why I need to filter and then sort the Met logbook (since there will be so many blanks, unuseful info and in the wrong order). (It will be in the wrong order because the "master logbook" has newer info at the top and I have more than one sheet of info for the master logbook) (so my links in the met logbook are somewhat out of order).
So what I need is to have code to filter column B with:
ã
and column H with "Blanks", then I need to sort column A (rec'd) in descending order (so that newer info will be at the top). I will post my Met Logbook sheet on my second post since its a little large.
View 49 Replies
View Related
Sep 22, 2008
Sub Custom3()
Range("A785:BW1455").AutoFilter Field:=2, Criteria1:="a"
Range("A785:BW1455").AutoFilter Field:=3, Criteria2:Range ("N").value
End Sub
Is Field 2 referring to Column B? So if I want to filter on the 9th row heading (going from left to right), my Field: = 9?
In the example above, the 1st criteria revolves filtering on dates with the goal of exceeding a certain date (i.e. > 1/1/08). Is there a way to reference the cell (which is in another workbook which is already open) in the criteria?
View 9 Replies
View Related
Nov 5, 2008
I want to make a filter started from row 2
the criteria should not be equal to B1
weekucase = UCase(Range("B1"))
Rows("2:2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:=" weekucase"
Rows("2:2").Select
Range(Selection.Offset(1, 0), Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
End Sub
But it doesn't work
View 9 Replies
View Related
