Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 114
Default INDEX and Scanning a Data Set in Excel

Greetings,

I'm using information found here (link below) to scan 200 rows of data and
return the rows that match a common key which I've defined on each row. Some
rows share the same key.

http://office.microsoft.com/en-us/ex...CL100570551033

This solution is working great except for one thing.

Here is a sample data set:

Key Version Period Customer
TYA_JAN This Year Actual January Band2 (26-32)
TYA_JAN This Year Actual January Band3 (33-39)
TYA_JAN This Year Actual January Band5 (47-53)
TYA_JAN This Year Actual January Band4 (40-46)
TYA_JAN This Year Actual January Band6 (54-60)
TYA_JAN This Year Actual January Band1 (-25)
TYA_JAN This Year Actual January Band7 (61-67)
TYA_JAN This Year Actual January Band8 (68+)
TYA_FEB This Year Actual July Band2 (26-32)
TYA_FEB This Year Actual July Band5 (47-53)
TYA_FEB This Year Actual July Band4 (40-46)
TYA_FEB This Year Actual July Band6 (54-60)
TYA_FEB This Year Actual July Band3 (33-39)
TYA_FEB This Year Actual July Band1 (-25)
TYA_FEB This Year Actual July Band7 (61-67)
TYA_FEB This Year Actual July Band8 (68+)
TYA_JUN This Year Actual June Band2 (26-32)
TYA_JUN This Year Actual June Band3 (33-39)
TYA_JUN This Year Actual June Band5 (47-53)
TYA_JUN This Year Actual June Band4 (40-46)
TYA_JUN This Year Actual June Band6 (54-60)
TYA_JUN This Year Actual June Band1 (-25)
TYA_JUN This Year Actual June Band7 (61-67)
TYA_JUN This Year Actual June Band8 (68+)
TYA_AUG This Year Actual March Band2 (26-32)
TYA_AUG This Year Actual March Band3 (33-39)
TYA_AUG This Year Actual March Band5 (47-53)
TYA_AUG This Year Actual March Band4 (40-46)
TYA_AUG This Year Actual March Band6 (54-60)
TYA_AUG This Year Actual March Band1 (-25)
TYA_AUG This Year Actual March Band7 (61-67)

Here is the formula I'd use to scan this set and return rows that match a
given key:

{=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW ($A$2:$A$129)),ROW(1:1)),N$22)}

The problem is that the formula above is pulling the second matching value
from the data set. Additionally, the last value in the result set is an
extra value that doesn't match the specified key.

More specifically, if the key being specified is "TYA_JUN" the result set
should be all rows from "This Year Actual" and "June". The results being
returned are as follows:

This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual March

I'm missing one June match at the top and the March value at the bottom
shouldn't be returned at all.

Any suggestions? I'm sure its in my index array formula but I don't know
enough about it to figure out what is wrong.

Thanks in advance for your help.

Regards,
Bryan






  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default INDEX and Scanning a Data Set in Excel

=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW( $A$2:$A$129)-MIN(ROW($A$2:$A$129))+1),ROW(1:1)),N$22)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bryan" wrote in message
...
Greetings,

I'm using information found here (link below) to scan 200 rows of data and
return the rows that match a common key which I've defined on each row.
Some
rows share the same key.

http://office.microsoft.com/en-us/ex...CL100570551033

This solution is working great except for one thing.

Here is a sample data set:

Key Version Period Customer
TYA_JAN This Year Actual January Band2 (26-32)
TYA_JAN This Year Actual January Band3 (33-39)
TYA_JAN This Year Actual January Band5 (47-53)
TYA_JAN This Year Actual January Band4 (40-46)
TYA_JAN This Year Actual January Band6 (54-60)
TYA_JAN This Year Actual January Band1 (-25)
TYA_JAN This Year Actual January Band7 (61-67)
TYA_JAN This Year Actual January Band8 (68+)
TYA_FEB This Year Actual July Band2 (26-32)
TYA_FEB This Year Actual July Band5 (47-53)
TYA_FEB This Year Actual July Band4 (40-46)
TYA_FEB This Year Actual July Band6 (54-60)
TYA_FEB This Year Actual July Band3 (33-39)
TYA_FEB This Year Actual July Band1 (-25)
TYA_FEB This Year Actual July Band7 (61-67)
TYA_FEB This Year Actual July Band8 (68+)
TYA_JUN This Year Actual June Band2 (26-32)
TYA_JUN This Year Actual June Band3 (33-39)
TYA_JUN This Year Actual June Band5 (47-53)
TYA_JUN This Year Actual June Band4 (40-46)
TYA_JUN This Year Actual June Band6 (54-60)
TYA_JUN This Year Actual June Band1 (-25)
TYA_JUN This Year Actual June Band7 (61-67)
TYA_JUN This Year Actual June Band8 (68+)
TYA_AUG This Year Actual March Band2 (26-32)
TYA_AUG This Year Actual March Band3 (33-39)
TYA_AUG This Year Actual March Band5 (47-53)
TYA_AUG This Year Actual March Band4 (40-46)
TYA_AUG This Year Actual March Band6 (54-60)
TYA_AUG This Year Actual March Band1 (-25)
TYA_AUG This Year Actual March Band7 (61-67)

Here is the formula I'd use to scan this set and return rows that match a
given key:

{=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW ($A$2:$A$129)),ROW(1:1)),N$22)}

The problem is that the formula above is pulling the second matching value
from the data set. Additionally, the last value in the result set is an
extra value that doesn't match the specified key.

More specifically, if the key being specified is "TYA_JUN" the result set
should be all rows from "This Year Actual" and "June". The results being
returned are as follows:

This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual March

I'm missing one June match at the top and the March value at the bottom
shouldn't be returned at all.

Any suggestions? I'm sure its in my index array formula but I don't know
enough about it to figure out what is wrong.

Thanks in advance for your help.

Regards,
Bryan








  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 114
Default INDEX and Scanning a Data Set in Excel

Bob you are a genius. I have no idea how this works but it does and I thank
you for that.

Bryan

"Bob Phillips" wrote:

=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW( $A$2:$A$129)-MIN(ROW($A$2:$A$129))+1),ROW(1:1)),N$22)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bryan" wrote in message
...
Greetings,

I'm using information found here (link below) to scan 200 rows of data and
return the rows that match a common key which I've defined on each row.
Some
rows share the same key.

http://office.microsoft.com/en-us/ex...CL100570551033

This solution is working great except for one thing.

Here is a sample data set:

Key Version Period Customer
TYA_JAN This Year Actual January Band2 (26-32)
TYA_JAN This Year Actual January Band3 (33-39)
TYA_JAN This Year Actual January Band5 (47-53)
TYA_JAN This Year Actual January Band4 (40-46)
TYA_JAN This Year Actual January Band6 (54-60)
TYA_JAN This Year Actual January Band1 (-25)
TYA_JAN This Year Actual January Band7 (61-67)
TYA_JAN This Year Actual January Band8 (68+)
TYA_FEB This Year Actual July Band2 (26-32)
TYA_FEB This Year Actual July Band5 (47-53)
TYA_FEB This Year Actual July Band4 (40-46)
TYA_FEB This Year Actual July Band6 (54-60)
TYA_FEB This Year Actual July Band3 (33-39)
TYA_FEB This Year Actual July Band1 (-25)
TYA_FEB This Year Actual July Band7 (61-67)
TYA_FEB This Year Actual July Band8 (68+)
TYA_JUN This Year Actual June Band2 (26-32)
TYA_JUN This Year Actual June Band3 (33-39)
TYA_JUN This Year Actual June Band5 (47-53)
TYA_JUN This Year Actual June Band4 (40-46)
TYA_JUN This Year Actual June Band6 (54-60)
TYA_JUN This Year Actual June Band1 (-25)
TYA_JUN This Year Actual June Band7 (61-67)
TYA_JUN This Year Actual June Band8 (68+)
TYA_AUG This Year Actual March Band2 (26-32)
TYA_AUG This Year Actual March Band3 (33-39)
TYA_AUG This Year Actual March Band5 (47-53)
TYA_AUG This Year Actual March Band4 (40-46)
TYA_AUG This Year Actual March Band6 (54-60)
TYA_AUG This Year Actual March Band1 (-25)
TYA_AUG This Year Actual March Band7 (61-67)

Here is the formula I'd use to scan this set and return rows that match a
given key:

{=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW ($A$2:$A$129)),ROW(1:1)),N$22)}

The problem is that the formula above is pulling the second matching value
from the data set. Additionally, the last value in the result set is an
extra value that doesn't match the specified key.

More specifically, if the key being specified is "TYA_JUN" the result set
should be all rows from "This Year Actual" and "June". The results being
returned are as follows:

This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual March

I'm missing one June match at the top and the March value at the bottom
shouldn't be returned at all.

Any suggestions? I'm sure its in my index array formula but I don't know
enough about it to figure out what is wrong.

Thanks in advance for your help.

Regards,
Bryan









  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default INDEX and Scanning a Data Set in Excel

All I did Bryan was to add MIN(ROW($A$2:$A$129))+1 to the part that was
using the row number as the index, as it was starting at 2 instead of 1
within the SMALL function.



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bryan" wrote in message
...
Bob you are a genius. I have no idea how this works but it does and I
thank
you for that.

Bryan

"Bob Phillips" wrote:

=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW( $A$2:$A$129)-MIN(ROW($A$2:$A$129))+1),ROW(1:1)),N$22)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bryan" wrote in message
...
Greetings,

I'm using information found here (link below) to scan 200 rows of data
and
return the rows that match a common key which I've defined on each row.
Some
rows share the same key.

http://office.microsoft.com/en-us/ex...CL100570551033

This solution is working great except for one thing.

Here is a sample data set:

Key Version Period Customer
TYA_JAN This Year Actual January Band2 (26-32)
TYA_JAN This Year Actual January Band3 (33-39)
TYA_JAN This Year Actual January Band5 (47-53)
TYA_JAN This Year Actual January Band4 (40-46)
TYA_JAN This Year Actual January Band6 (54-60)
TYA_JAN This Year Actual January Band1 (-25)
TYA_JAN This Year Actual January Band7 (61-67)
TYA_JAN This Year Actual January Band8 (68+)
TYA_FEB This Year Actual July Band2 (26-32)
TYA_FEB This Year Actual July Band5 (47-53)
TYA_FEB This Year Actual July Band4 (40-46)
TYA_FEB This Year Actual July Band6 (54-60)
TYA_FEB This Year Actual July Band3 (33-39)
TYA_FEB This Year Actual July Band1 (-25)
TYA_FEB This Year Actual July Band7 (61-67)
TYA_FEB This Year Actual July Band8 (68+)
TYA_JUN This Year Actual June Band2 (26-32)
TYA_JUN This Year Actual June Band3 (33-39)
TYA_JUN This Year Actual June Band5 (47-53)
TYA_JUN This Year Actual June Band4 (40-46)
TYA_JUN This Year Actual June Band6 (54-60)
TYA_JUN This Year Actual June Band1 (-25)
TYA_JUN This Year Actual June Band7 (61-67)
TYA_JUN This Year Actual June Band8 (68+)
TYA_AUG This Year Actual March Band2 (26-32)
TYA_AUG This Year Actual March Band3 (33-39)
TYA_AUG This Year Actual March Band5 (47-53)
TYA_AUG This Year Actual March Band4 (40-46)
TYA_AUG This Year Actual March Band6 (54-60)
TYA_AUG This Year Actual March Band1 (-25)
TYA_AUG This Year Actual March Band7 (61-67)

Here is the formula I'd use to scan this set and return rows that match
a
given key:

{=INDEX($A$2:$J$129,SMALL(IF($A$2:$A$129=$O$18,ROW ($A$2:$A$129)),ROW(1:1)),N$22)}

The problem is that the formula above is pulling the second matching
value
from the data set. Additionally, the last value in the result set is
an
extra value that doesn't match the specified key.

More specifically, if the key being specified is "TYA_JUN" the result
set
should be all rows from "This Year Actual" and "June". The results
being
returned are as follows:

This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual June
This Year Actual March

I'm missing one June match at the top and the March value at the bottom
shouldn't be returned at all.

Any suggestions? I'm sure its in my index array formula but I don't
know
enough about it to figure out what is wrong.

Thanks in advance for your help.

Regards,
Bryan











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
How can I make reports faster in excel using ADO ST Excel Discussion (Misc queries) 1 July 29th 06 02:03 AM


All times are GMT +1. The time now is 11:05 AM.

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"