Copy / Filter Data Based On Data In Column Occuring X Times

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 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. 200, yahoo 120, etc... I need it to show me the 200 rows of emails and extract them to a new sheet so that I can then follow up with and see if they are interested in the corporate version.)

View 2 Replies


Get Value Occuring Maximum Times And Its Count

Aug 14, 2009

I have a column with over 60,000 rows of data. I need to find out which value in the table occurs the maximum number of times, and its count.

The traditional methods of COUNTIF or INDEX/MATCH using MODE dont seem to work and excel crashes after a few mins.

Is there any other way to do the same (other than splitting the file into several smaller files)?

View 9 Replies View Related

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.

I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.

View 2 Replies View Related

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

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.


[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.


[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.


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

Copy And Paste Data Based On Yes Or No In Column?

Jan 30, 2014

I have an Excel Spread Sheet that lists all of the people who have been issued Keys in your workplace, so the row has multiple information columns (Name, Department, Key Code, etc). One row is titled "Left Workplace" and you can select either a Yes or a No. Based on that selction I want it to copy and paste into a different spreadsheet (Either Inactive - if "No" is selected or Active - if "Yes" is selected) What is the best way to go about this?

View 2 Replies View Related

Copy Data Based On Column Label

Jun 16, 2014

I am new in Excel. Recently I have to copy a cell data from Pivot table (example value in B1 cell) to new sheet by this week. My problem is this value could move to C1 by next week & so on. I try to use the column label instead of hard coding like cell column name (B1).

View 1 Replies View Related

Copy And Paste Values Based On Column Data

Jan 28, 2014

I would like to copy and paste the values from a worksheet (HS-Detail) in a file to different tabs depending on the value in column A (Regions). I have a macro that works but it pastes the formulas and it takes quiet a while to run due to the number of records. How can I alter this to paste just the values and speed up the macro?

[Code] ......

View 3 Replies View Related

Macro That Will Copy Data Based On The Column Headings

Apr 8, 2009

i need a macro that will copy data based on the column headings, i have two worksheets, the first worksheet is the raw data and second worksheet will be the results sheet, the issue is the headings i have in raw data is different to the headings i have in the results sheet and the heading will not always be in same column in the results sheet, so for example in Raw data i have heading Quantity and in destination sheet i have heading Units, so the macro should copy the data for quantity in sheet raw data and paste to sheet "Results' under heading "Units". The header row on both sheets is row 3. I do have more headers but if I know how to do one then i can apply the same method for the others.

Sheer raw data

Quantity 820,655.00 177,015.00 6,652,041.00 1,517,267.00

Sheet results

Units 820,655.00 177,015.00 6,652,041.00 1,517,267.00

View 9 Replies View Related

Macro To Copy Data In Column To Another Worksheet Based On Date?

Mar 4, 2013

I have a workbook with 2 worksheets being used. Sheet 1 has the months of the year starting with Jan in A1, Feb in B1 etc. On Sheet 2, I have a column of information. The first cell in the column eg. B1 has data validation list based on the months on Sheet 1. The information in the rest of the colum on Sheet 2 needs to be copied to the relevant colum on Sheet 1

View 5 Replies View Related

Select And Copy Set Of Data In Column Based On Starting And Ending Values

Aug 26, 2013

I have a macro that copies three sections of data from a word document and pastes it within the Excel worksheet. The column of data is pasted in a single column (data to be space-delimited and evaluated later), and what I would like the MACRO to do is separate the three sections of data based on the starting and ending values of the section and place them in different columns.

Section 1 : starts with the word "Team"
Section 1 : ends with the word "City"

Section 2 : starts with the word "Location"
Section 2 : ends with the word " Date"

Section 3 : starts with the word "Member"
Section 3: ends with the word "Age"

So section 1 would be pasted into cell C1, section 2 pasted into G1 and section 3 pasted into J1.

Each section will have vary in number of rows between the starting/ending values, so to cut and paste the section based on the starting and ending value is vital.

View 7 Replies View Related

Copy Data Table Seperate Sheets Based On Column Criteria

May 12, 2008

I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.

View 9 Replies View Related

Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

View 14 Replies View Related

Filter Data And Copy

Sep 9, 2006

=+IF( 'Bella Vida_DRH'!$E72=9,'Bella Vida_DRH'!A72,"")

I am trying to create a sheet that will search for all the rows that contain a 9 in a particular cell

then it will copy that row into a new sheet

if not =9 then it should go and check the next row etc. etc.

this formula works for one cell but doesn't goto the next cell if the value is false

I would really appreciate if some one could point me in the right direction

View 9 Replies View Related

Filter And Copy Data

Feb 19, 2007

Is there a way i can filter down data using dates and then copy to another worksheet. So lets i want copy all things with todays date on it and paste it to another worksheet.

View 2 Replies View Related

Calculating Difference Between Two Times Through A Column Of Data

Aug 21, 2013

It will be easier to explain in an example:

Time (hh:mm:ss) Digital
1 10:03:00 0
2 10:03:01 0
3 10:03:02 1
4 10:03:03 1
5 10:03:04 1
6 10:03:05 0


From the data above I am searching for the duration of when the digital column says '1', i.e my function = A5-A3 which would output 2 secs or 00:00:03 & likewise A9-A8.

The problem I have is that the digital signal is staggered and does not always have the same frequency. I have over 6848 lines of time to check so to do this manually would take me all day.

View 8 Replies View Related

Excel 2010 :: Insert Row Data N Times Based On Cell Value

May 1, 2012

I have a sheet that contains the following columns:
Invoice, Document #, Date, PO #, Part #, Part Description, Quantity, Net Amount

Based on the quantity in the row I need to copy the row, and insert it n-1 times. So if the quantity is 5, I need to copy and insert the data below the original row 4 times for a total of 5 rows of data.

I plan on firing the macro with a button as the data will change month to month.

Using Windows 7 and Excel 2010

View 2 Replies View Related

Copy And Paste From Filter Data?

Jun 9, 2014

How can we copy and paste filter

For Example I have copied table data and want to paste same date in J and K column then how can I do this ?

File is attached for ref : Copy and Paste.xls‎

Actually my Basic question is how can I copy data from filtered data and paste in visible cells only

View 8 Replies View Related

VBA Filter Data And Copy To Worksheets?

Oct 5, 2013

I want to filter for high value items in Sheet Raw Data which meet the below criteria, if criteria met then copy data to the criteria worksheets

High value criteria

>=1,000,000 =1M=5,000,000 =5M=10,000,000 then copy to sheet >=10M

See below example

RawData  ABCDEFGHIJKL1GroupCategorySet IDValue DateEntry DateTypeAmountAUD EquivalentCCYAgeSourceRef12TESTTESTTEST21-Jan-1223-Jan-12LCR1,000,000.001,000,000.00AUD2TESTTEST3TESTTESTTEST24-Jan-1224-Jan-12LCR-3,500,000.00-3,500,000.00AUD1TESTTEST4TESTTESTTEST23-Jan-1223-Jan-12SDR5,600,000.005,600,000.00AUD2TESTTEST5TESTTESTTEST24-Jan-1224-Jan-12SDR-

[Code] .........

View 9 Replies View Related

Filter Data On One Sheet And Copy To Another

Jul 27, 2006

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!

View 9 Replies View Related

Filter Column A To Include Multiple Cells Of Data In Column D?

Mar 31, 2014

Is there a way to filter column A but ensure it includes multiple lines of data in a different column.

I have attached an example of what I am looking for:

I need to filter by community but keep all the information listed in columns, B, C, & D.
So when I filter to Thunder Bay I would still see the 5 members listed in column D.

I realize the easy solution is to fill all the empty cells in column A to pick up the data in the other columns, but I do not want to Fill all the empty cells to include the name "Thunder Bay" (not the look I want for this spreadsheet).

View 2 Replies View Related

Creating Macro To Filter And Copy Data

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

Macro Filter Data & Copy To New Workbook

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

Auto Filter Data & Copy To Another Sheet

Jan 2, 2010

macro which autofilter data & copy to another sheet.

below mention are the steps, i dont knw how to write vb code to autofilter month. Please find sample workbook on
below mention link

1.Auto Filter Date 2(Column C)

2.Select First Month (eg.May 09)

3.Auto Filter Date 1 (Column B)

4.from, the month, which filter in Date 2 (from May 09 to Dec 09..last month of year)

5.Auto Filter Column A

6.Copy each unique value on output sheet

View 9 Replies View Related

Tally Data And Return The Number Of Times A Particular Value Is Present In Column

Aug 25, 2009

I have a formula that I use to tally data and return the number of times a particular value is present in column C.


I copy this formula down the sheet until arriving at the last input in column "C".
Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.

View 9 Replies View Related

Identifying Data In One Column Based On Highest Figure Data In Another Column?

Jun 19, 2014

I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.

In a previous post i was shown how to identify the highest grade


I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E

However, I also want to identify the subject in which they scored their highest grade in column F

I have two problems

1. I don't know how to write a formula that brings in the subject based on the grade for each student

2. I don't know how to write a formula in case there is a tie (see DAVID L)

View 11 Replies View Related

Filter Data Based On Header

Nov 15, 2012

On sheet1 I got a lot of columns with data, starting from row 8, which is the header.
On sheet2 I'd like to retrieve the filtered data, based on the value mentioned in cell A1 (on sheet2)

So if for example cell A1 on sheet2 shows "Peter", the code should filter the data on sheet1 with "peter" in the header.

The filtering should be based on cell color (red in this case)
The filtered data should then be copied to sheet2 starting from A3

View 3 Replies View Related

How To Filter And Copy Data From One Sheet And Paste To New Worksheet

Jun 2, 2008

can excel do this (see the attachment pls). if possible can someone show me how to do that. i am new in excel vba.

View 9 Replies View Related

VBA To Filter Data Based On Contents Of Range

Apr 24, 2014

I'm trying to add a line of code to filter some data based on a date in the range cell C3.

This is my code;

[Code] ....

It doesn't seem to work.

View 4 Replies View Related

Filter Subsets Of Data Based On A Flag

Feb 25, 2007

I have a list of items in column A. And a "flag" in column B. I have attached a small sample. What I need to be able to do is "Hide" all the items in column A where there are no flags for that item in column B. This I can do. The problem is that if a flag shows for an item, I want to show ALL of the rows containing that item, even if there are no flags for some of them, and hide the rest.

View 3 Replies View Related

How To Make Filter On Data Column By Using Value

May 1, 2012

I have 2 date values in excel spreadsheet ( i must use it by the sheet) and i want to flter table by the date column and AutoFilter, when the start date and the end date will be taken from the sheet itself.

but when i defined date variable like starta and edit the filter (Criteria1:=">=starta") it didt work (the value was starta and not the date in the starta) how to edit it?

sub abcd
Dim starta As Date, enda As Date
starta = Worksheets("periods and options").Range("a1").Value
enda = Worksheets("periods and options").Range("b1").Value
Selection.AutoFilter Field:=1, Criteria1:=">=starta", Operator:=xlAnd _
, Criteria2:="

View 1 Replies View Related

Copyrights 2005-15, All rights reserved