Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Filtering data in cells Results

Does anyone know a sneaky way to highlight different values in a single column of unsorted data? VBA or non-VBA answers are welcome.

I've already tried the following:

(1) Advanced filtering for unique records. Works only if the data is sorted, not unsorted.

(2) Conditional formats. Highlights different values in unsorted data, but I'd like to select these different values after they've been highlighted. There doesn't seem to be a way to do this apart from adding a column next to each data column, inserting formulas that flag the different data in the adjacent column, selecting the formula results, and offsetting the selection back to the data column. (http://www.experts-exchange.com/Appl..._21484217.html)

I'd like to avoid looping through rows or cells in the used range, as this will be too slow for my data.

Thanks very much.

Hi every1

Using this site I have discovered how to use the SUBTOTAL function to count the rows in a filtered list. I have attached a file showing a basic version of my spreadsheet.

Column F holds the date that a request is received. I need my users to filter this column to get requests received between certain dates. However, I need to exclude some rows in this count.

Column E by default results in the number 12, but depending on the data input in column D can result in a number between 8 and 13 (13 is a means of showing that it should no longer be counted for the purposes of my spreadsheet).

As you can see cell K1 counts the total or filtered records based on the data in column E (this column will be hidden from users).

Is there anyway I can count the total/filtered rows excluding all rows with the number 13 in column E?

I have seen, read and got very confused with threads on this site discussing the PRODUCT function with SUBTOTAL. If this is the way to go could someone explain it to me?

Thanks

Philip

In one excel file (with one sheet for source data), we're currently filtering the source data to pour into 60-80 identical shell reports, each report occupying one sheet in this file. While we've developed macros to do some of this work, we'd like to develop one super macro that takes its instructions from the contents page (the first sheet in the workbook). Specifically, what we're hoping is to loop row by row through the contents sheet, taking three different instructions from each row. We see the macro looking for the named column heading in the data sheet (as specified in cell A1 on the contents page), then to perform the filtering macro (as specified on the contents page in B1) on the particular column in the data sheet, noting that we've already developed a number of named filtering macros, then to paste the filtered data into the worksheet (as named on the contents page in C1), then to loop to the second row of the contents page, and so on until empty. As this is an exercise that we repeat for every project we do, it would sure help to streamline our efforts, and to reduce the potential for human error. We don't know, first, if this can be done, and second, how to include the cells in code. Thanks in advance.

I have a worksheet for shipping schedules and need a macro to filter/sort by multiple cell values in multiple columns and copy all full rows selected to a new file in the same worksheet format. Not all column's cell values need to be filtered/sorted. We do this sort and file creation today manually using the auto-filter feature in xl2000. Schedules are kept in monthly workbooks/files with weekly worksheets/tabs. Worksheets have 18 to 20 columns and can grow to about 400 rows. Each schedule section is added/entered by destination (i.e. customer orders). We format everything as text currently and use the auto-filters constantly. Each row represents one shipping order and that data must be kept intact on both the master worksheet and the new worksheet created. The new worksheet will be named, e-mailed via Lotus Notes :{ and saved in a specific folder on our local network.

The filter/sort criteria is simple (origin, carrier, destination) but I have not been successful in writing the macro to include all possible occurences (origin, carrier, etc.) if they are present and still run if some are not present, correctly. Unfortunately, I am not yet experienced in code but this seems it should be workable as just a recorded macro. I have recorded a number of them but cannot seem to get it right. I either come up with orders missing or excessive blank worksheets/files being created.

Step Actions:
1. Upon completion of initial entry of a batch of orders, auto-filter is on and desired 'Destination' is selected/filtered.
2. Step through 'Origin' column (i.e. loading points in column 3), filtering and creating a new file for each different value or origin. Name file (Origin & Week #) and set-up as an attachment in a new e-mail message in Lotus Notes (V6.5).
3. Then step through 'Carriers' column (column 16), filtering and creating a new file for each different value or origin. Name file and set-up as an attachment in a new e-mail message in Lotus Notes.
4. Then create a new file for the 'Destination' column value already selected/filtered. Name file and set-up as an attachment in a new e-mail message in Lotus Notes.
5. Remove all auto-filters and leave master file open with active worksheet displayed.

Operator will then manually address each e-mail and send. My next step will be to try and automate this to address the e-mail also, if possible.

Would appreciate some guidance.

Im kinda new using VBA, but i need to filter a row of data and then copy the cells that pass the filter to a different sheet. The problem is that everycell in the row of data needs that passes the criteria needs to be coppied together with the cell next to that. Ive tried to loop this, and it tests all the cells but only copies the first cell. The code is now as follows


	VB:
	
 CommandButton2_Click() 
     
    Dim cell As Range 
    For Each cell In Range("e2:e100") 
         
        If cell > 117.8 And cell < 118.2 Then cell.Select 
        Selection.Copy 
         
        Sheets("enter").Select 
        Range("h2:i2").Select 
        ActiveSheet.Paste 
         
    Next cell 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
every cell in the range of e2:e100 needs to be tested if it is between 117,8 and 118,2, if this is so, then the cell that falls in these criteria PLUS the cells next to it (the F row), needs to be coppied to the H row.
At this moment it scans all the cells for this criteria but then only coppies the cell from the e row to h2.

thanks...

Hello,
I am trying to filter out a large amount of data, currently I am only searching one column("C:C"), but for another function, I am needed to filter through 3 columns (C,D,E). I also need to revise my code, so that if the cell contains any part of the string I am looking for, it should filter it out(keep the row), and if not, it should delete the row. Right now, this code will search col c, and if that row EQUALS my search variable (FVar), it will keep the row. (set it to true).

Function Filter4(FVar)
Dim rnData As Range
Dim iRows As Integer
Dim iColumns As Integer

Application.ScreenUpdating = False
Range("A1").Select
Set rnData = ActiveSheet.UsedRange
Let iRows = rnData.CurrentRegion.Rows.count
Let iColumns = 6

Cells(1, iColumns).FormulaR1C1 = "Sort"
Cells(2, iColumns).Select
'Selection.FormulaR1C1 = "=OR(RC[-3]=""Var1"",RC[-3]=""Var2"",RC[-3]=""Var3"",RC[-3]=""Var4"",RC[-3]=""Var5"",RC[-3]=""Var6"",RC[-3]=""Var7"",RC[-3]=""Var8"",RC[-3]=""Var9"",,RC[-3]=""Var10"",RC[-3]=""Var11"",RC[-3]=""Var12"",,RC[-3]=""Var13"")"
Selection.FormulaR1C1 = "=OR(RC[-3]={""" & FVar & """})"
Selection.Copy Destination:=Range(Cells(3, iColumns), Cells(iRows, iColumns))

With rnData
.AutoFilter Field:=iColumns, Criteria1:="False"
.Offset(1, 0).Resize(.Rows.count - 1, .Columns.count) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

rnData.Columns(iColumns).Delete
Application.ScreenUpdating = True

End Function

Would anyone be able to help me out in order to be able to search through 3 columns, and even if the cell contains part of the search variable, it will filter it out??? thanks alot for any suggestions that you may have!!

Hi everyone,

I am trying to filter a list of data by using the date in a reference cell.

I have done this before without a problem, but this is the first time I have tried to do this using a date. For some reason the data is filtering, but it all goes away and does not filter correctly. After I filter this data using the date, I will be copying the filtered data to another workbook.

I have attached a sample so you can see what I am talking about.

Thanks for any input you can give me with this problem.

Excelpower

The purpose of the spreadsheet is to print one report for each employee who missed one of the three key metrics from the day before.

I don't know what the most logical way of accomplishing this task is, but this is what I came up with. Unfortunately, I only have a grasp on step one, and even then I'm not real sure of myself.

1) Set the Date field in PivotTable1, PivotTable2, and PivotTable3 to the date displayed cell I2.

2) For every data point in PivotTable1 that is greater than the value entered in C6, copy the employee's name into the list starting in cell K9.

3) For every data point in PivotTable2 that is less than the value entered in cell F6, copy the employee's name into the list starting in cell K9.

4) For every data point in PivotTable3 that is greater than the value entered in I6, copy the employee's name into the list starting in cell K9.

5) For every unique name in the list starting in cell K9, change the "Name" field in PivotTable4 and print the worksheet. This process should repeat until all names have had an individual report printed.

Again, if there's a better way of accomplishing my goal, I'm open to suggestions! Thanks in advance for any time you spend looking at my problem.

Jas.

Hi I am a newbee to this site and trying to improve my knowledge and coding.
I have a worksheet with various items in each column. This code filters the selected listbox item (listbox11) and shows the results in another listbox (listbox13). How do you filter by 2 listbox values example listbox11 and say another listbox12 and display results in listbox13. Of course if user only selects item from one listbox it would also filter by that one box as it is currently doing.


	VB:
	
 CommandButton12_Click() 
    Dim sCriteria As String, rngList As Range, sRngAddress As String 
     
    sCriteria = Me.ListBox11.Value 
     
    With Sheet1 
        .AutoFilterMode = False 
        .Range("A5:g100" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=1, Criteria1:=sCriteria 
        Set rngList = .Range("A5:g" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible) 
        Sheet3.Cells.ClearContents 
        rngList.Copy Sheet3.Range("a4") 
        sRngAddress = "Sheet3!" & .Range("a5").CurrentRegion.Address 
    End With 
     
    With Me.ListBox13 
        .RowSource = sRngAddress 
    End With 
     
    Set rngList = Nothing 
     
End Sub 

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

Hi,

I have a column in which i am filtering it with a specific value . and then paste a data in front of those values. But when i autofilter it with the value , and then paste values , the values goes in the hidden cells rather than only going in the filtered cells ,
Can anyone help with this .

The situation is explained below, I have a column with kind of data shown, i want to autofilter the 9S containing cells and the place a data in front of the cell containing 9S only

This is the original condition

9S255 9S63 9S35 9S5846
This is after autofilter

9S255 9S63 9S35 9S5846

And i want to paste a data in front of autofiltered cells only , but when i paste a data it also goes between the cells those are filtered out.

Can anyone help with this ,
Thanks in advance

Using a macro, I need to filter on a column using a partial string value. For example, I want to filter on the value 'abc' where the data in the column contains cells with values like 'aaa-abc-bbb'.

Hello again, Wizards of Oz(grid)!

Back again to ask inane questions and irritate.. This time, I have a scrap of code I have manipulated to do my bidding, and need a piece to add in to it. My current code:


	VB:
	
 PosPPD() 
     
    Set rd = Sheets("ROLL UP") 
    Set wd = Sheets("+PPD") 
     
    For i = 1 To rd.Range("P250").End(xlUp).Row '// set i to the last row in column A
        If UCase(Cells(i, 16)) = "P" Then '//if cell in column 16 matches "P" then...
            wd.Cells(wd.Range("A25").End(xlUp).Row + 1, 1) = rd.Cells(i, 2) '//write data from rollup column 2 to +PPD column
1
            wd.Cells(wd.Range("B25").End(xlUp).Row + 1, 2) = rd.Cells(i, 3) '//write data from rollup column 3 to +PPD column
2
        End If 
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
looks at the sheet "Roll Up" in the "PPD" column, checks for any "P" codes(positive PPD), then copies the name(Cell 2 of that row) and the social security number (Cell 3 of that row) and then pastes the name and social to the next available row in cell 1 and 2 on the "+PPD" sheet. The issue I have is that pressing the magic button more than once just propagates the same names repeatedly, leading to having the same person on the sheet as many times as there are button presses. I need a line of code added that checks the name and social against the list that is already ON the "+PPD" sheet in column 1(Name) and column 2(Social) and bypasses it if it already matches BOTH criteria. I will start scrubbing a copy of my Medical tracker to post here, but it contains over 2000 social security numbers, names, and medical histories, and the code directly reads the socials and names, so it won't be easy...have patience.(Created a new med tracker with completely fake info in it here:Test Medical Tracker.xlsm) I'm also going to peruse the "possible answers" that show up, but I know someone in here will go "Wow, this question is super easy to answer, what an idiot!" and spit out a line of code in 30 seconds or less...which is what I am here to learn. Long-winded and over-explanatory answers are always accepted, I like to know the "why this works" and "how this works" to better understand and learn this stuff.

Thanks in advance,
Shen

Dear experts,
I have a set of data with test scores in column A. I need to count the cases with the scores greater or equal than a predetermined value, say, in cell F1. The formula should count only visible (non-filtered cells). The range is dynamic to include all data in the column.
I have been trying to use an example with the SUBTOTAL formula, but it would not work for me. Any changes to the formula suggested?

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A2,0,0,COUNTA(A:A),1))),--(OFFSET(A2,0,0,COUNTA(A:A),1)>=F1))

Thanks a lot in advance.

Test Result (Cell A1)

51.28
78.24
98.80
69.49
59.81
68.76
57.80
63.97
55.02
62.30
90.84
86.73
73.83

Hi Team,

I am new for this site :- so might be this is already been posted my applogies if i am posting it again in different header.

Help Required:-

I have data in which i have Mobile number / Address / Name of Party in different cells.

I am looking for extract mobile number from it. hope some of you may able to help me in this regards.

*** I am attaching data file which contain two sheets with different format which will give you better understanding of my requirement.

Data.zip

Thanking you in advance.

I am working for a college and have over 12,000 records in an excel spreadsheet and about 24 columns/fields. I need to filter the data in this sheet according to 7 of these colums, e.g. course code, course title etc. I want to be able to select the courses etc from a drop down list which will populate some of the other cells in the criteria i want to use for filtering (i.e. the cell of course title will automatically be populated if a particular code is selected) I want the list to be narrowed down i.e. if a particular cost centre is selected the list of the courses that are in that cost centre should narrow down automatically instead of displaying all the course codes.
I want the filtered data to then be displayed in a table which can also be used to edit the original data.
i am attaching a sample of what i need to make. I tried using pivot tables but they simply give summaries which i dont need. I am really desperate as i need to figure this out ASAP and submit it in a few hours

So the find method works on visible and hidden rows depending if you use LookIn:=xlValues or xlFormulas; however, it does not work on hidden rows caused by a filter.

I have a range that is filtered, and I want to find a value in a column that may or may not be the filter criteria (and thus hidden). I do not want to show all the data, do the find, then reapply all the filters again. I could loop through the column, but I am looking to see if Excel offers a more efficient way.

Thank you.

Okay I am REALLY new to creating macros. I am just barely starting my tutorial training, so I need to know how to fix my first attempt at writing my own macro from scratch.

I need to hide rows based on a cell value. In cell B4 I list how many students are in period one.

I have enough rows for 35 students found in A10:A44. In C10: C44 I listed numbers 1-35.

So I need any row that has a number in the C column higher than what was entered in B4 to be hidden, but when cell B4 changes I need rows to unhide if their row is less than new value.

So here was my attempt:


	VB:
	
 HideRows() 
    BeginRow = 10 
    EndRow = 44 
    ChkCol = 3 
     
    For RowCnt = BeginRow To EndRow 
        If Cells(RowCnt, ChkCol).Value > (Range("B4")) Then 
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True 
        Else 
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False 
        End If 
    Next RowCnt 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then I am going to need to add to it for period 2-8 data.

Example: Period 2 amount of students is entered in C4.

And the rows with period 2 student names are 52-86 etc. etc...

Hi,
I have a basic doubt with regard to filters in excel.

1) Suppose i mark a set of columns for filtering,i do the filtering.would i be able to have another set of filters in excel without removing the previous one.How can i know excel limits this i.e can it be seen on the sheet.

2)Is it possible to copy the values in the data range (with the hidden cells) and having the same kind of filtering elsewhere in the excel sheet.

Hi there,

I've gone through the search function on the forum a few times to try and find a solution, but I'm struggling to find an appropriate answer - it may just be I can't ask the search the right question.

Having read through the help section and warnings a few times, I hope I'm not going to upset anyone with my 1st post...

My situation is as follows. I have one master sheet of data, with a large number of fields and data.

I need to turn this master data into individual records, each record exisiting as an individual worksheet - lets call it a 'U'. The U is a template sheet which has calculations and lookups built into it to complete further information. The completed U's are then used by a number of people for different reasons. There are 3 main 'flavours' of these sheets which have slightly different uses.

I've gone from knowing nothing about macros to having learned enough about them in the last week or so to populate each individual sheet with the data, and save the new file in the location I want it to go.

What I want to do now is filter the fields displayed by the individual U sheets, as not every field is applicable to each 'flavour'. I've marked up the rows as to the appropriate flavour - e.g. Row 17 is applicable to 'P' 'F' and 'R' (Cell which is auto filtered contains PFR), but Row 18 is only 'P' and 'F' (Cell contains PF).

I've gone through the master file and identified each entry as a the appropriate flavour - to summarise what I'd like to do now:

1) Automatically populate the template file with the relevant data. (which my macro will do)
2) Use an autofilter to filter the rows equal to the data in the reference sheet so these are the only ones displayed. Eg. Reference sheet says 'P', so I want to filter the U sheet where autofilter column contains the letter 'P'
3) Rename the file and save as my reference in the location I want it to (which the macro is doing).

Here's what I've got:

	VB:
	
Range("CF3").Select 
Selection.Copy 
Windows("USS iss1.xls").Activate 
Range("G158").Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell, Operator:=xlAnd 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(where CF3 = the cell in the master data with the flavour in it, "USS iss1" is the template U file, G158 is a spare cell and Autofilter Field 1 contains the row reference which tells me which data applies to which flavours).

I've tried using the macro recorder, which when I paste the value in the autofilter/contains box records it as the value I've just put in rather than a copy of the reference cell. I've tried

	VB:
	
Criteria1:=*ActiveCell* 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and other variations on the same theme, but to no avail - I get "Compile error: Expected:expression".

Once again, I hope i've not fallen foul of the forum rules, and I'm conscious I've waffled on too much with my explanation. I'd really appreciate any assistance on this subject that is out there...

Matt

Basically, what I'm trying to do is copy filtered data. I select my column and copy it, filtered, no problem. But then my code bugs out when it tries to copy the next column.


	VB:
	
 
     
    Sheets("Mam Roll-up").Activate 
     
     'Copy CEID's.
     
    Range("L4:L134").Select 
     
    Selection.SpecialCells (xlCellTypeVisible) 
     
    Selection.Copy 
     
    Windows("OSC MP CT Indicators Master.xls").Activate 
     
    Sheets("CT data P1264").Activate 
     
    Range("A2").Select 
     
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
     
    :=False, Transpose:=False 
     
     
     
     'Copy CEID Goals.
     
    Range("R4:R134").Select 
     
    Selection.SpecialCells (xlCellTypeVisible) 
     
    Selection.Copy 
     
    Windows("OSC MP CT Indicators Master.xls").Activate 
     
    Sheets("CT data P1264").Activate 
     
    Range("G2").Select 
     
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
     
    :=False, Transpose:=False 
     
End If 

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


"Selection.SpecialCells (xlCellTypeVisible)" works in the first block, but bugs out on the second. Any ideas?


No luck finding an answer? You could always try Google.