+ Reply to Thread
Results 1 to 17 of 17

Assistance with Filtering out -m- tags from a Index

  1. #1
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Assistance with Filtering out -m- tags from a Index

    Assistance with Filtering out -m- tags from a Index when searching through a column of computers.

    Here is my current code:
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$K$2:$K$2001<>"6")*(DataDump!$B$2:$B$2001="Managed"),ROW(DataDump!$K$2:$K$2001),""),ROWS(L$2:L2))),"")

    Breakdown
    DataDump!A:A - is the list of computers to look through
    DataDump!$K$2:$K$2001 - is the list of version numbers to look through and to show any version that is NOT 6.
    DataDump!$B$2:$B$2001 - A list that will say either managed or unmanaged

    This code above is currently working,
    It looks through the list of computers, to find anything that is NOT a version 6, then looks for "Managed" in Column B, and then posts the resulting computer (That is managed and NOT version 6)

    How can add to this formula to "ONLY review computers in DataDumpA:A that DO NOT CONTAIN -m-

    I tried:
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$A$2:$A$2001<>"*-M-*")*(DataDump!$K$2:$K$2001<>"6")*(DataDump!$B$2:$B$2001="Managed"),ROW(DataDump!$K$2:$K$2001),""),ROWS(L$2:L2))),"")

    but it didn't work
    My end game is for this not to count Macs

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Assistance with Filtering out -m- tags from a Index

    did you enter using CTRL SHIFT ENTER?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    yes of course

  4. #4
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    Was testing:
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$A$2:$A$2001<>"*-M-*")*(DataDump!$K$2:$K$2001<>"6")*(DataDump!$B$2:$B$2001="Managed"),ROW(DataDump!$K$2:$K$2001),""),ROWS(L$2:L2))),"")

    I dropped the 2nd and 3rd query so now its:
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$A$2:$A$2001<>"*-M-*"),ROW(DataDump!$K$2:$K$2001),""),ROWS(L$2:L2))),"")

    It still doesn't work. It returns a computer but it shows the computers with -M- in them still. I got a feeling this is something really easy and right under my nose.

  5. #5
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    This didn't work either:
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$K$2:$K$2001<>"6")*(DataDump!$B$2:$B$2001="Managed"),IF(DataDump!$A$2:$A$2001<>"*-M-*",ROW(DataDump!$K$2:$K$2001),""),""),ROWS(L$2:L2))),"")

  6. #6
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    ok so further into the research it would appear it doesn't want to take the Wild Card. Now I just gotta find a way around this...

  7. #7
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    Ok, so here is where the issue is"

    Original code: (Works)
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$K$2:$K$2001<>$A$10)*(DataDump!$B$2:$B$2001="Managed")*(DataDump!$Q$2:$Q$2001<>"Mac OS X"),ROW(DataDump!$K$2:$K$2001),""),ROWS(L$2:L2))),"")

    When removing the asterisk it fails and displays all computers including the Mac's, it refuses to use a wild card.
    =IFERROR(INDEX(DataDump!A:A,SMALL(IF((DataDump!$K$2:$K$2001<>$A$10)*(DataDump!$B$2:$B$2001="Managed")*(DataDump!$Q$2:$Q$2001<>"Mac*"),ROW(DataDump!$K$2:$K$2001),""),ROWS(L$2:L2))),"")

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    Re: Assistance with Filtering out -m- tags from a Index

    Try


    =IFERROR(INDEX(DataDump!$A$1:$A$2001,SMALL(IF(NOT(ISNUMBER(SEARCH("*M*",DataDump!$Q$1:$Q$2001)))*(DataDump!$K$1:$K$2001<>"6")*(DataDump!$B$1:$B$2001="Managed"),ROW(DataDump!$K$1:$K$2001),""),ROWS(L$1:L1))),"")

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assistance with Filtering out -m- tags from a Index

    You typically don't need wildcards with the SEARCH function.

    NOT(ISNUMBER(SEARCH("*M*",DataDump!$Q$1:$Q$2001)))

    Can be written as:

    ISERR(SEARCH("M",DataDump!$Q$1:$Q$2001))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    Re: Assistance with Filtering out -m- tags from a Index

    @Tony, I'm certain you have told me this before .... but memory isn't what it was!!

  11. #11
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    This corrected it.
    In the end, this is what I used:
    =IFERROR(INDEX(DataDump!$A$1:$A$2001,SMALL(IF(ISERR(SEARCH("M",DataDump!$Q$1:$Q$2001))*(DataDump!$K$1:$K$2001<>$A$10)*(DataDump!$B$1:$B$2001="Managed"),ROW(DataDump!$K$1:$K$2001),""),ROWS(L$1:L1))),"")

    Thank you
    I will read up on using the ISERR and SEARCH functions for future reference.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assistance with Filtering out -m- tags from a Index

    Quote Originally Posted by JohnTopley View Post
    memory isn't what it was!!
    I hear ya!

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assistance with Filtering out -m- tags from a Index

    You're welcome. We appreciate the feedback!

  14. #14
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    Tony, out of curiosity.
    We used IF(ISERR(SEARCH("M",DataDump!$Q$1:$Q$2001)) to remove any computers that have a -M- in them.

    How would you do that in reverse?
    Like only show computers that have the -M- but nothing else?

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assistance with Filtering out -m- tags from a Index

    Replace:

    ISERR

    With:

    ISNUMBER

    That will return TRUE for every cell in the range that contains the letter M anywhere within the cell.

  16. #16
    Forum Contributor
    Join Date
    05-17-2012
    Location
    Silver Spring
    MS-Off Ver
    Excel 2016
    Posts
    212

    Re: Assistance with Filtering out -m- tags from a Index

    Cool, good to know. Thank you again Tony!

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Assistance with Filtering out -m- tags from a Index

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index and Match formula assistance
    By bigroo1958 in forum Excel General
    Replies: 9
    Last Post: 12-11-2014, 01:50 PM
  2. Assistance with formula (countif,sum,index)
    By omv_80 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-30-2014, 11:20 AM
  3. Index + match assistance!
    By LemonOrange in forum Excel General
    Replies: 12
    Last Post: 04-02-2014, 03:39 PM
  4. [SOLVED] Index & Match Assistance
    By Excel-Access in forum Excel General
    Replies: 1
    Last Post: 12-10-2012, 05:50 AM
  5. Excel VBA filtering assistance
    By tonypanda in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-26-2012, 04:46 AM
  6. Would like to ask assistance in filtering multiple pivots at the same time
    By polaro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2005, 09:57 PM
  7. Filtering assistance
    By Ted Metro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1