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.)
Is there a way to easily filter the data according to the products' "index %" of a given month and then copying all data (including name, price, index%, and index value for the whole year) of those deviating from 100,0% to a new sheet?
I tried this myself with auto filters but the layout presented some problems. The sheet is created automatically by another programme, so the layout is what it is.
I really hope someone might have a solution for this since I need to sort through these sheets, with thousands of products, every month by hand!
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....
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.
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 am using code to filter my 4 sheets Greater then 0 (zero)
After apply above filter now i need to copy multiple rows and paste on another specific workbook for paste i m using below code:
for 1st sheet with the name ("V2")
for 2nd sheet with the name("LV")
For 3rd sheet with the name ("F2")
and 4th sheet with the name("L2")
If I play above code one by one all is going very well,,,,,,or if use in this way all is going very well
But here is a big problem..........if any sheet have no value greater then 0(zero)....then code paste all data... e.g shssts("LV") .Range("C5:C54").Copy but C5:C54 have no data greater then 0(zero) and it will paste on another sheet c5:c54 and again new sheets data will paste below the c54 while c5:c54 have no data.
So I want if any sheet have no data with range is greater then 0(Zero) then skip the copy paste code or use like SpecialCells(xlCellTypeVisible) .
I have filter in three columns, column F, Col J, and Col L. I need to filter the range based on my input and copy the filtered range. My input will remain same for all three filters, it should copy the filtered range based on my input and paste in sheets (4) of my workbook.
I am expecting three filtered range in sheets(4) one after another with teh header data.
How to solve table with filter which needs to be copy to other tab.
Example: One tab has table with filter. Issue is with another tab which needs to be copy but issue is that another tab will further investigate with additional columns. Can be solved this issue as first tab will filter and change all rows order and another tab should read content with added columns.
Run other code here, then loop back and choose the next item on the list
The problem is the ActiveCell.FormulaR1C1 = "D12549.256", the range if cells with the items I need to use is Range("O1:O24"), that is what I want to use since the data will change and needs to be dynamic.
I seem to be having an odd problem with Advanced Filter. When I use the Copy to Another Location option I get an error that:
"The extract range has an illegal or missing field name".
As far as I can tell, there are no blank or missing fields. I did originally have a DATE field but I have renamed this in case it was a "reserved" name but still have the problem.
When I used the filter in place it works correctly.
I am trying to use Advance Filter as opposed to lots of looping, copying and pasting and this is the only snag. I suppose that I could filter in place then copy/paste the results to the other sheet but I'd like to see where I may be going wrong.
I thought I had this cracked last month when I ran a set of reports using some code dependent on using advanced filters to first create a unique list to loop through and then to create a list of all entries in a list for each unique entry. The idea being that I extracted all the data for each unique entry in the list and saved that to a new workbook, named after the unique entry.
However, this month I'm totally flummoxed again. I can't seem to get even the first part, creating a unique list, to run.
I have a sheet with several thousand rows of data with maybe 25 columns. I have the following code to try to extract the unique entries in the 2nd column, column B and store them in the same worksheet in column AD, to use when looping through the list.
VB: With ActiveSheet .Range("AD1").EntireColumn.Delete .Range("A1").CurrentRegion.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range( _
I need to automate the attached as per the following (by not recording macro unfortunately).
Sheet one has a dump of data. First, I need to change the data format from yyyymmdd to dd/mm/yyyy. Then I need to filter for region EU and only copy the filtered data from columns A, C-D, F and the difference from columns I-J to Sheet 2. Then in Sheet 2, I need another column to calculate the difference per unit.
Sheet one contains a table of table from C6:AY7000
On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.
On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.
So BN3 = Sheet2C3 And BN4= Sheet2C4
I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.
This is the coding I have written to select and filter the correct data values.
Sub AddFilter() ' ' AddFilter Macro ' Dim rCrit1 As Range, rCrit2 As Range With Application .EnableEvents = False .ScreenUpdating = False
This now gives me the correct data in the table.
I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY
The copied data will then be pasted into sheet 2 starting in cells B9
I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.
now i have filtered data in a table, i want to use the results by printing off a table showing just these and the appropriate rows from other tables on worksheets. these all have a specific ID which is how they are traceable to each other. like a related field in an access database. two tables/worksheets are like so:
table1: Reg No | Rank | Name | Initials | Troop | etc