Free Microsoft Excel 2013 Quick Reference

Counting filtered results

I have a file that I filter. Once filtered
I would lik to count only the results
It always counts all of the data whether in the results or not
I tried a countIF but it only gives one criteria.

In the Origine column I want to filter the cells that contain Bagotville or Montreal
In the Destinaire column I want to filter the cells that also contain Bagotville or Montreal.
I used an automatique filter and the did a custom filter. I now want to count the results. How many rows do I now have on the screen. The hidden ones don't interest me.
Any help would be greatly appreciated


I have made two Marcros 8 and 9 below. Neither completes the requirement of attaching the value of number of rows from a filtered sheet.

I attempted to use .UsedRange.Rows.Count and it returned total rows, not filtered rows.

To get around this in Marcro9 I put a =Countif(T:T,"K") in cell T2 of the spreadsheet. I then took the value from T2 and used it in my Macro. I don't like this option as it leaves my spreadsheet open to users moving rooms and the value stored in T2 being lost.

What is the VBA code to perform the Countif or Count.rows for filtered results?

Thank you for any help in advance.


	VB:
	
 Macro8() 
    Dim num_Rows As Integer 
    With Sheets("Data") 
        num_Rows = .UsedRange.Rows.Count 
    End With 
     
    Windows("SO CIM March 18 working.xls").Activate 
    Rows("5:5").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Rows("5:" & num_Rows).Select 
    ActiveSheet.Paste 
End Sub 
 
Sub Macro9() 
     
    Dim num_Rows As Integer 
     
    With Sheets("Data") 
        num_Rows = Range("T2").Value2 + 4 
    End With 
    Windows("SO CIM March 18 working.xls").Activate 
    Range("A5").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Copy 
    Range("A6:A" & num_Rows).Select 
    ActiveSheet.Paste 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, is there a way to count found result from FIND in vba?
I want a msgbox found count.
Suppose if there are 278 words called "Ext1" in finding range i want vba to retuen result in msgbox.

Thanks for helping.

Pedie

Hi all -

Trying to paste filtered results except header row
My code below pastes the header row for "Details" 2x.
I tried altering the row to row 2, but that caused incorrect results to be returned.

Detail - Sales
First I filter the data range for "Total Sales"
Paste the results with header row to wsDetails

Details - Details
Then filter the data again for "N"
I would like to paste these results to wsDetails w/o the header

Thanks
-marc


	VB:
	
 
 
Sub comp_pl_ytd_Totals() 
    Dim wbBook As Workbook 
    Dim wsData As Worksheet 
    Dim wsTotals As Worksheet 
    Dim wsDetails As Worksheet 
    Dim wsExtract As Worksheet 
    Dim rngData As Range 
    Dim rngCrit As Range 
    Dim rngDest As Range 
    Dim arrCrit 
    Dim myRange As Range 
    Dim C As Range 
    Dim lngrows As Long 
    Dim strFormula As String 
    Dim rngCase As Range 
     
    With Application 
        .ScreenUpdating = False 
        .DisplayAlerts = False 
        .Calculation = xlCalculationManual 
    End With 
     
    Set wbBook = ThisWorkbook 
    Set wsData = wbBook.Worksheets("Data") 
    Set wsTotals = wbBook.Worksheets("Totals") 
    Set wsDetails = wbBook.Worksheets("Details") 
    Set wsExtract = wbBook.Worksheets("Extract") 
    lngrows = wsData.Range("A65536").End(xlUp).Row 
    Set rngCase = wsData.Range("C2:C" & lngrows) 
     
    For Each C In rngCase 
        If UCase(C.Value) = C.Value Then 
            C.Offset(0, 13).Value = "Y" 
        Else 
            C.Offset(0, 13).Value = "N" 
        End If 
    Next 
     
     'Clear
    wsTotals.UsedRange.Clear 
    wsDetails.UsedRange.Clear 
     
     'Totals
    Set rngData = wsData.Range("A1:P" & lngrows) 
    Set rngDest = wsTotals.Range("A1") 
     
    arrCrit = Array("Switches", "Y") 
    Set rngCrit = wsData.Range("IV1:IV2") 
     
    rngCrit = WorksheetFunction.Transpose(arrCrit) 
     
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False 
    rngCrit.ClearContents 
     
     'Details - Sales
     
    Set rngData = wsData.Range("A1:P" & lngrows) 
    Set rngDest = wsDetails.Range("A1") 
     
    arrCrit = Array("Category", "TOTAL SALES") 
    Set rngCrit = wsData.Range("IV1:IV2") 
     
    rngCrit = WorksheetFunction.Transpose(arrCrit) 
     
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False 
    rngCrit.ClearContents 
     
     'Details - Details
     
    Set rngData = wsData.Range("A1v:P" & lngrows) 
    Set rngDest = wsDetails.Range("A65536").End(xlUp).Offset(1, 0) 
     
    arrCrit = Array("Switches", "N") 
    Set rngCrit = wsData.Range("IV1:IV2") 
     
    rngCrit = WorksheetFunction.Transpose(arrCrit) 
     
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False 
    rngCrit.ClearContents 
     
     'Column widths
    wsTotals.UsedRange.Columns.AutoFit 
    wsDetails.UsedRange.Columns.AutoFit 
     
     
     'Cleanup
    Set wbBook = Nothing 
    Set wsData = Nothing 
    Set wsTotals = Nothing 
    Set wsDetails = Nothing 
    Set wsExtract = Nothing 
    Set rngData = Nothing 
    Set rngCrit = Nothing 
    Set rngDest = Nothing 
    Set arrCrit = Nothing 
    Set myRange = Nothing 
    Set C = Nothing 
     
     
    With Application 
        .ScreenUpdating = True 
        .DisplayAlerts = True 
        .Calculation = xlCalculationAutomatic 
    End With 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,
I have a folder with 250 files. Each of the file has only one column. I need to search the rows starting with "Party Name" and copy them in any one row. I want a macro either to copy the filtered results in the same file or a fresh workbook. Please help.My file looks like this:
Column A
row 1 ABCDE
row 2 FGHI
row 3 Party Name:Abcd
row 4 JKLM
row 5 nopq
row 6 STUV
row 7 Party Name:ryz
row 8 Party Name:mno
row 9 XYZ

I want the results as below:

Column A Column B Column C Column D
row 1 ABCDE Party Name:Abcd Party Name:ryz Party Name:mno

Hello,

This is my first question here, and after searchign your excellent forums - I couldnt find the answer. Hopefully someone can help...

I am trying to use the SUMPRODUCT command, and ensure it is updated based on the filtered results set.

Basically I have 3 columns. The first is a persons name, the second is a percentage, and the third a value. I have X hundred rows, and basically need to sum the first column multiplied by the second column for the total rows available. This works using the SUMPRODUCT function. However...

When I filter the results based on someones name - the SUMPRODUCT function does not update based on the filtered results - like the SUBTOTAL command.

Is there any way to get this to work using formulae please????

Any help much appreciated....
Thanks...

Hi all

I have a large workbook with about 65000 rows of data. I have the Autofilter turned on. When the user opens the workbook a userform is display where they can select the filter for column A and then search the results in Column K. However the bit of code I use to search Column K is searching the entire column and not the filtered results. the code I use for the search is:

	VB:
	
 Search_Click() 
     
    Dim rngFind As Range 
    Dim strWild As String 
    Dim rngSearch As Range 
    Dim vntWords As Variant 
    Dim intWordIndex As Integer 
     
     
    Set rngSearch = Range("k1:k20000") 
     ' any match
    vntWords = Split(searchbox.Text, " ") 
    For Each rngFind In rngSearch 
        For intWordIndex = LBound(vntWords) To UBound(vntWords) 
            If Trim(vntWords(intWordIndex))  "" Then 
                If InStr(1, rngFind.Text, vntWords(intWordIndex), vbTextCompare) > 0 Then 
                    SearchResults1.ListBox1.AddItem rngFind.Value 
                    SearchResults1.ListBox1.List(SearchResults1.ListBox1.ListCount - 1, 1) = rngFind.Address 
                    Exit For 
                End If 
            End If 
        Next 
    Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And the results are displayed on another form. What should I set the Set rngSearch = Range (???) to?? This needs to be the results of what the user filters in column K. I.e. it needs to search through what is shown in column k on the workbook after the filter and not all the hidden entries as well.

Any ideas would be really appreciated as I've tried a number of different things here and none work.

Hello,

I have a question as to how can you count the results of formulas. I have a Colume with if formulas, if there is a matching value they return "1". I want a formula that counts the number of cell where 1 was returned. the colume with the if formulas is A

thanks

Is there a way to create a macro to automatically graph filtered results of an advanced filter?

Currently, I have to select that data manually and it isn't very efficient.

Any suggestions?

I have a worksheet that has a pivot table, I have many other worksheets that I would like to display the filtered results from the pivot table. What is the best way.

Should I create many multiple pivot tables one for each record of information?

Is there a VB code that will copy the filtered data from the Pivot Table source the specific worksheet and increase the appropriate number of rows?

Hi

I'm trying to produce a graph based on filtered results.

I have a macro that autofilters the data ( ComboBox_Change) to give the
number of respondents to various promotional schemes.

One of the data columns ("Wards") is a list of parts of the town. The
graph is to show the number of respondents, by scheme, by ward,
defaulting to the whole data table if no scheme is chosen.

I have the graph source data picking up the wards info, but this shows
the total number of applicants by ward, and doesn't change when the
ComboBox_change macro filters the data.

I've had a look through past messages on this group, and it seems to
require a pivot table, but that is just waaaay over my head.

Does anyone have any practical advice?

TIA

Steve

Hello everyone:

I have a question about excel that I am sure someone here can
answer....

I have a table set up with a lot of data, and that uses autofilters to
choose which data to show based on various criteria. I know that I
can print the filtered result quite easily by setting the filters to
the criteria I want, and then printing.

I would like to make this easier for other people in my company, who
are not so familiar with the use of filters in excel. Specifically, I
would like to set up a separate print page (i.e. - separate worksheet in
the front) that contains a number of drop down lists. The drop down
lists are set up using the Data...Validation commands, and correspond
to the headers at the top of my list.

I would like to build a macro (or find a function) that takes the
results of the Drop-Down Lists and then (i) automatically transfers
them to become the "filtered results"- (i.e. - resets the filters based
on the results of the drop down list) and then prints out the result for
the user. The users then don't have to touch they filters; all they
have to do is make their choices on the Drop Down lists.

I guess a key would be to set the filters automatically using the
results of the DropDown list.

Can anyone help me on this? Feel free to email me at

If a use an autofilter on a long list of data, it shows the filtered items at
the top of the page with blue line numbers, but it also shows the remaining
results underneath. Is there anyway of just showing the filtered results ?
thanks

How do I set the filter so that I can copy only the filter results vs. the
filter results & the underlying data that was hidden.
--
Mauren

I have several tables in the same tab. I would like to be able to apply
independent autofilters to each table and display filtered results for
several tables at once.
I tried to search online but didn't find any tips on how to do this. Is this
possible in Excel 2003?
Thanks in advance!

Hello!

I'm trying to count the result of two columns match.

=SUMPRODUCT(('Commerce'!$H$1:$H$1000=$B5)*('Commerce'!$J$1:$J$1000=D$4))

Work's fine like this! But the thing is, it's comparing *exactly* with the B5 and D4 cells. Actually I want to make a lookup in the cell to see if there's the word in it, like:

=COUNTIF('Commerce'!$H:$H;"*" & B9 & "*")

Do you guys know if it's possible?

Thanks,
efilipe.

Dear Excel Geeks,

I have a request pretty close to the one mentioned in this thread.
http://www.excelforum.com/excel-prog...ml#post2399345

I have two other requests.

1. The first one is very similar to the one you answered. The only change is that I want the data to be pasted under a heading instead of adding a new sheet each time.

Please see my excel sheet "MS Excel-Filtered Results in Single New Sheet.xls" for better understanding.

2. Also the results that are pasted in a new excel sheet as shown in above example need to be saved as text files with the top name as separate text files, as shown in zip file "MS Excel-Filtered Results in New Text Files.zip".

Please see my example text files for better understanding.

Thanks in advance for the help!

Pavan

Ok, this is my first post on this forum.

Background:

I have a file with....

1) Columns A to CN populated with data, mostly numeric
2) Much of this numeric data is zeros, some columns almost entirely so, but others have some variability. Many records have the same value, so there is a quite a bit of redundancy in the numeric values
3) There are about 50,000 records
4) I've applied the Filter Command across the top row so I can see all the unique values for each column when I click on each dropdown arrow.

Question:

Rather than clicking on each dropdown and typing the column heading and each unique value present in that column into a new sheet (so I can see them all at once), I'd like to export all the unique values (filter results) for each column in a new sheet. I don't really care which data record a value belongs to, I just want to document the range of variability in each column. Is there a way to capture or export column headings and each unique present in each column only?

If a use an autofilter on a long list of data, it shows the filtered items at
the top of the page with blue line numbers, but it also shows the remaining
results underneath. Is there anyway of just showing the filtered results ?
thanks

I searched the forum and google for this, but haven't found anything. Seems like it should be relatively simple.

How do I rank filtered results? I do not wish to use VBA if possible.

I am trying without success to create a macro that will display filtered results in another worksheet. Attached is a copy of the file scenario. There are several macro attempts within the file but I cannot seem to set autofilter ranges

Basically for tyhe activesheet in workbook (except control sheet) the range a3 to last entry in column s or t needs to be filtered by column 3 with the criteria1 being "M" (for major work)
That filtered result would need to be pasted to the next available row in worksheet result, and then the autofilter to be turned off

explanation repeated in control sheet of workbook attached
Any help would be appreciated

I posted a different approach to the same problem in the Excel 2007 by mistake, and have been unable to remove, I hope the moderators will overlook this as I have tried to contact them to remove the post so I could repost here in the programming section

Many thanks Nigel

When I filter in excel, sometime the filter results show in the bottom left
hand corner and sometimes they don't. Why is that?

Hi All,

One part of a macro that I wam working on filters a column where the contents of the cells is N/A.

How do I display the text from these filtered columns only in a label on a form.

Filtered results as follows:
A, B, C
Garry, Smith, N/A
Fred, Blogs, N/A
How do I capture the contents of the cells containing "Garry" and "Fred" and display them in a form label? The number of cells to be captured will need to be dynamic depending on the number of N/A' that are shown in the filter.

Thanks
Garry

Is it possible for one of the count or sum commands to only count filtered data using the autofilter command?

Hi, I have a worksheet that has a large table that I filter by a certain column. Is it possible to populate a table on a second worksheet that is the filtered result of the first worksheet's table?

For instance, if I have a table listing types of cars and their color / make / etc. I sort it to see only Red cars. Is it possible to have my second worksheet populated with a table of red cars, and my third worksheet populated with a table of green cars?