+ Reply to Thread
Results 1 to 6 of 6

To Call All Value Exclude value consist of special words

  1. #1
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    271

    To Call All Value Exclude value consist of special words

    Hi All,

    I have below formulas to call up all value in column F.
    When the formulas do the call up, is it possible to exclude value consist of CW, AWP, TMP?

    My Column F will look like below:-
    B5 - 3
    CW - E
    A2 - 3
    B4 - 1
    CW - D
    A2 - 2
    B2 - 2
    CW - D
    AWP - C
    CW - E

    Please Login or Register  to view this content.
    Hope some one able to help me on the code.

    Thanks in advance

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: To Call All Value Exclude value consist of special words

    Hi,

    =IF($A5>$C$5, "", INDEX('Tele-Visit Report'!F$1:F$1000, SMALL(IF(('Tele-Visit Report'!$H$1:$H$1000=$B$5)*(ISERROR(SEARCH("CW",'Tele-Visit Report'!$F$1:$F$1000))*(ISERROR(SEARCH("AWP",'Tele-Visit Report'!$F$1:$F$1000))*(ISERROR(SEARCH("TMP",'Tele-Visit Report'!$F$1:$F$1000))))),ROW('Tele-Visit Report'!F$1:F$1000), ""), ROWS($A$1:$A1))))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: To Call All Value Exclude value consist of special words

    Hi,

    Thanks you very much for your fomulas. It work very well.
    I have modify it to fix all column. It work really good but found there is an error of #NUM! which i was unable to fix it.

    Would appreciate if you could help.

    I attached my worksheet here for easy reference.
    Attached Files Attached Files
    Last edited by cyee; 06-27-2013 at 05:43 AM. Reason: attached wrong fild

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: To Call All Value Exclude value consist of special words

    Hi,

    Please explain in words to me the function of the part of the formula in C5 highlighted in red below:

    COUNTIFS('Tele-Visit Report'!B:B, "<>", 'Tele-Visit Report'!H:H, B5)

    I do not recoginse the use or meaning of "<>" in this case - not equal to what?

    Regards

  5. #5
    Forum Contributor
    Join Date
    09-02-2010
    Location
    singapore
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: To Call All Value Exclude value consist of special words

    Actually innitially i need to have the Total amount of person. But I dont need this anymore in this worksheet.
    So you may delete it.

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: To Call All Value Exclude value consist of special words

    Two of your six entries in column F of the Tele-Visit Report sheet contain "CW", which you asked to be excluded from the results. #NUM! in this case simpy means that, after returning the matching four entries, there is no fifth, sixth, etc. to be put in these rows.

    You can either mask this with an IFERROR, or, since it appears that whoever set this up for you is using cell C5 for this purpose (hence why I asked you about the formula in this cell), you would need to amend that to include these new criteria, i.e. in C5:

    =SUMPRODUCT(('Tele-Visit Report'!$H$1:$H$1000=$B$5)*(ISERROR(SEARCH("CW",'Tele-Visit Report'!$F$1:$F$1000))*(ISERROR(SEARCH("AWP",'Tele-Visit Report'!$F$1:$F$1000))*(ISERROR(SEARCH("TMP",'Tele-Visit Report'!$F$1:$F$1000))))))

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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