+ Reply to Thread
Results 1 to 4 of 4

Match Single Numeric Criteria and Return Multiple Numeric Labels

  1. #1
    Sam via OfficeKB.com
    Guest

    Match Single Numeric Criteria and Return Multiple Numeric Labels

    Hi All,

    I have two Columns of data: Column "E" houses the Numeric Labels and Column
    "F" houses the Numeric Values. Column "F" will have genuine duplicate values
    that should be included in the Returned results. The criterion value will
    vary.

    Desired Result:
    I would like to find / match all values (duplicates included) that are equal
    to a specific criterion value - eg: 1 (one) in Column "F" and then have all
    their corresponding adjacent Numeric Labels returned from Column "E" to a
    single cell, if possible. If not, then returned to individual cells on the
    same Row.

    Example Data:
    Col E Col F
    30 8
    35 1
    37 3
    40 1
    45 10
    50 2
    53 4
    57 11
    60 5
    62 1

    Expected Results:
    Matching criterion value of 1 (one) Labels 35, 40 and 62 should be returned
    to either a single cell or individual cells on the same Row.

    Thanks
    Sam

    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200512/1

  2. #2
    Domenic
    Guest

    Re: Match Single Numeric Criteria and Return Multiple Numeric Labels

    I believe having the corresponding values returned to a single cell
    would require either VBA or the use of the function MCONCAT which is
    available in the free add-in Morefunc.xll.

    Since I'm not familiar with VBA, and the add-in is not available for my
    Mac version of Excel, I can only offer you a solution where the
    corresponding values are returned to individual cells...

    Assuming E1:F10 contains your data...

    1) Let H1 contain your criterion, such as 1

    2) Enter the following formula in I1 and copy across...

    =IF(COLUMNS($I1:I1)<=COUNTIF($F$1:$F$10,$H1),INDEX($E$1:$E$10,SMALL(IF($F
    $1:$F$10=$H1,ROW($E$1:$E$10)-ROW($E$1)+1),COLUMNS($I1:I1))),"")

    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <599cd29802c04@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
    wrote:

    > Hi All,
    >
    > I have two Columns of data: Column "E" houses the Numeric Labels and Column
    > "F" houses the Numeric Values. Column "F" will have genuine duplicate values
    > that should be included in the Returned results. The criterion value will
    > vary.
    >
    > Desired Result:
    > I would like to find / match all values (duplicates included) that are equal
    > to a specific criterion value - eg: 1 (one) in Column "F" and then have all
    > their corresponding adjacent Numeric Labels returned from Column "E" to a
    > single cell, if possible. If not, then returned to individual cells on the
    > same Row.
    >
    > Example Data:
    > Col E Col F
    > 30 8
    > 35 1
    > 37 3
    > 40 1
    > 45 10
    > 50 2
    > 53 4
    > 57 11
    > 60 5
    > 62 1
    >
    > Expected Results:
    > Matching criterion value of 1 (one) Labels 35, 40 and 62 should be returned
    > to either a single cell or individual cells on the same Row.
    >
    > Thanks
    > Sam


  3. #3
    Sam via OfficeKB.com
    Guest

    Re: Match Single Numeric Criteria and Return Multiple Numeric Labels

    Hi Domenic,

    Thank you for reply.

    Using the Array Formula below, it only returns the first Numeric Label that
    matches the criterion. The first matched Numeric Label is returned several
    times across the Row.

    >Assuming E1:F10 contains your data...


    >1) Let H1 contain your criterion, such as 1


    >2) Enter the following formula in I1 and copy across...


    >=IF(COLUMNS($I1:I1)<=COUNTIF($F$1:$F$10,$H1),INDEX($E$1:$E$10,SMALL(IF($F
    >$1:$F$10=$H1,ROW($E$1:$E$10)-ROW($E$1)+1),COLUMNS($I1:I1))),"")


    >...confirmed with CONTROL+SHIFT+ENTER.


    I've checked to see if I made any typo's but cannot spot any (yet!).

    Would the Worksheet Function TRANSPOSE help?

    Any further assistance much appreciated.

    Cheers
    Sam

    Domenic wrote:
    >I believe having the corresponding values returned to a single cell
    >would require either VBA or the use of the function MCONCAT which is
    >available in the free add-in Morefunc.xll.
    >
    >Since I'm not familiar with VBA, and the add-in is not available for my
    >Mac version of Excel, I can only offer you a solution where the
    >corresponding values are returned to individual cells...
    >
    >Assuming E1:F10 contains your data...
    >
    >1) Let H1 contain your criterion, such as 1
    >
    >2) Enter the following formula in I1 and copy across...
    >
    >=IF(COLUMNS($I1:I1)<=COUNTIF($F$1:$F$10,$H1),INDEX($E$1:$E$10,SMALL(IF($F
    >$1:$F$10=$H1,ROW($E$1:$E$10)-ROW($E$1)+1),COLUMNS($I1:I1))),"")
    >
    >...confirmed with CONTROL+SHIFT+ENTER.
    >
    >Hope this helps!
    >
    >> Hi All,
    >>

    >[quoted text clipped - 29 lines]
    >> Thanks
    >> Sam


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200512/1

  4. #4
    Sam via OfficeKB.com
    Guest

    Re: Match Single Numeric Criteria and Return Multiple Numeric Labels

    Hi Domenic,

    Please ignore my previous Post. The Formula works Great!

    My error: I entered the Formula and then copied it across the various cells
    and then confirmed with CONTROL+SHIFT+ENTER , when all the cells were still
    selected.

    I should have entered the Formula in one selected cell and then confirmed
    with CONTROL+SHIFT+ENTER, copying the Formula across the relevant cells
    "AFTER" the CONTROL+SHIFT+ENTER was performed in the first cell.

    Thank you for all your help - very much appreciated.

    Cheers,
    Sam

    Domenic wrote:
    >I believe having the corresponding values returned to a single cell
    >would require either VBA or the use of the function MCONCAT which is
    >available in the free add-in Morefunc.xll.
    >
    >Since I'm not familiar with VBA, and the add-in is not available for my
    >Mac version of Excel, I can only offer you a solution where the
    >corresponding values are returned to individual cells...
    >
    >Assuming E1:F10 contains your data...
    >
    >1) Let H1 contain your criterion, such as 1
    >
    >2) Enter the following formula in I1 and copy across...
    >
    >=IF(COLUMNS($I1:I1)<=COUNTIF($F$1:$F$10,$H1),INDEX($E$1:$E$10,SMALL(IF($F
    >$1:$F$10=$H1,ROW($E$1:$E$10)-ROW($E$1)+1),COLUMNS($I1:I1))),"")
    >
    >...confirmed with CONTROL+SHIFT+ENTER.
    >
    >Hope this helps!
    >
    >> Hi All,
    >>

    >[quoted text clipped - 29 lines]
    >> Thanks
    >> Sam


    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...tions/200512/1

+ 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