Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 30th 05, 04:09 PM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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   Report Post  
Old December 30th 05, 05:30 PM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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 <[email protected], "Sam via OfficeKB.com" <[email protected]
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   Report Post  
Old December 30th 05, 06:59 PM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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),INDE X($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),INDE X($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   Report Post  
Old December 30th 05, 09:01 PM posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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),INDE X($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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match Single Numeric Criteria and Return Multiple Numeric Labels Sam via OfficeKB.com Excel Worksheet Functions 0 December 29th 05 09:44 PM
Count Intervals of 1 Numeric value in a Row and Return Count down Column Sam via OfficeKB.com Excel Worksheet Functions 8 October 4th 05 04:37 PM
How can I retrieve multiple rows that match one criteria, i.e. a d Morgs Excel Worksheet Functions 3 September 1st 05 03:19 PM
match multiple criteria & return value from array Tat Excel Worksheet Functions 2 June 21st 05 04:31 PM
Return result from multiple criteria Pat Excel Worksheet Functions 6 December 16th 04 04:39 PM


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017