Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Match Each Numeric occurrence and Return Individual Rows of Data

Hi All,

Match Each Numeric occurrence of ONE Criterion and Return Individual Rows of
Data (Multiple Columns):
the Numeric Criterion may only appear ONCE in a Row, if at all.

I have a Dynamic List called "History" that spans 7 Columns and many Rows on
Sheet1:
1st Column houses ID - numeric value, formatted General
2nd Column houses Date - numeric value, formatted 26/9/2005
3rd-7th Five Columns house numeric values: single/double-digit, formatted
General.

I would like a Formula to search every Row of the 3rd-7th Column of the
Dynamic List "History" for each occurrence of a particular single-digit or
double-digit numeric criterion by using an Input Cell for flexibility (so I
may specify whatever numeric criterion to match within the List).

Each matched occurrence of the specified numeric criterion should Return the
following data from its Row within the Dynamic List "History": ID, Date and
actual Numeric Value.

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.

Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1
  #2   Report Post  
Domenic
 
Posts: n/a
Default

In article <55beba6df9c83@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.


I'm not sure what you mean by, "...and Numeric Value". Each row
contains 5 numeric values. Which one do you want? Or do you want the
total?
  #3   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

Thanks for reply.

I'm not sure what you mean by, "...and Numeric Value". Each row
contains 5 numeric values. Which one do you want? Or do you want the
total?


"...and Numeric Value"

This is the Numeric Criterion that is being matched.

Which one do you want?

The value of the individual Numeric Criterion that is being matched.

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.


Cheers
Sam

Domenic wrote:
To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.


I'm not sure what you mean by, "...and Numeric Value". Each row
contains 5 numeric values. Which one do you want? Or do you want the
total?



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1
  #4   Report Post  
Domenic
 
Posts: n/a
Default

First, define three separate dynamic ranges. One for the column
containing the ID, one for the column containing the Date, and one for
the columns containing your values (all five columns). Once you've done
that, try the following...

A1: enter your criteria/numeric value

B1:

=SUM(--(MMULT(--(Values=A1),TRANSPOSE(COLUMN(Values)^0))0))

....confirmed with CONTROL+SHIFT+ENTER

C1, copied down:

=IF(ROWS(C$1:C1)<=$B$1,INDEX(ID,SMALL(IF(MMULT(--(Values=$A$1),TRANSPOSE(
COLUMN(Values)^0)),ROW(ID)-MIN(ROW(ID))+1),ROWS(C$1:C1))),"")

....confirmed with CONTROL+SHIFT+ENTER

D1, copied down:

=IF(ROWS(D$1:D1)<=$B$1,INDEX(Date,SMALL(IF(MMULT(--(Values=$A$1),TRANSPOS
E(COLUMN(Values)^0)),ROW(Date)-MIN(ROW(Date))+1),ROWS(D$1:D1))),"")

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

In addition, you can also define a reference (Insert Name Define)
for this part of the formula...

MMULT(--(Values=$A$1),TRANSPOSE(COLUMN(Values)^0))

....and replace it with the name you've given it.

Hope this helps!

In article <55bf8a838b3d7@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thanks for reply.

I'm not sure what you mean by, "...and Numeric Value". Each row
contains 5 numeric values. Which one do you want? Or do you want the
total?


"...and Numeric Value"

This is the Numeric Criterion that is being matched.

Which one do you want?

The value of the individual Numeric Criterion that is being matched.

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.


Cheers
Sam

  #5   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

Solution is Great! Thank you very much for your time and assistance.

Cheers,
Sam

Domenic wrote:
First, define three separate dynamic ranges. One for the column
containing the ID, one for the column containing the Date, and one for
the columns containing your values (all five columns). Once you've done
that, try the following...

A1: enter your criteria/numeric value

B1:

=SUM(--(MMULT(--(Values=A1),TRANSPOSE(COLUMN(Values)^0))0))

...confirmed with CONTROL+SHIFT+ENTER

C1, copied down:

=IF(ROWS(C$1:C1)<=$B$1,INDEX(ID,SMALL(IF(MMULT(--(Values=$A$1),TRANSPOSE(
COLUMN(Values)^0)),ROW(ID)-MIN(ROW(ID))+1),ROWS(C$1:C1))),"")

...confirmed with CONTROL+SHIFT+ENTER

D1, copied down:

=IF(ROWS(D$1:D1)<=$B$1,INDEX(Date,SMALL(IF(MMUL T(--(Values=$A$1),TRANSPOS
E(COLUMN(Values)^0)),ROW(Date)-MIN(ROW(Date))+1),ROWS(D$1:D1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

In addition, you can also define a reference (Insert Name Define)
for this part of the formula...

MMULT(--(Values=$A$1),TRANSPOSE(COLUMN(Values)^0))

...and replace it with the name you've given it.

Hope this helps!

Hi Domenic,

[quoted text clipped - 15 lines]
Cheers
Sam



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/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
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
Count Intervals of 2 Numeric values in same Row and Return Count across Row Sam via OfficeKB.com Excel Worksheet Functions 12 September 24th 05 10:58 PM
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM


All times are GMT +1. The time now is 02:09 PM.

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

About Us

"It's about Microsoft Excel"