Excel: Amending INDEX Statements 1

[shytott]

Hi
I have 2 INDEX statements which extract a maximum of 2 data items from an array of 12 items. I now need to be able to extend this extraction to upto12 items from an array of 18.

The current two statements are:
=INDEX($CR$8:$DC$8,1,MATCH("M",$CR$9:$DC$9,0))
=INDEX($CR$8:$DC$8,1,MATCH("M",OFFSET($CR$9,0,MATCH("M",$CR$9:$DC$9,0),1,12),0)+MATCH("M",$CR$9:$DC$9,0))

These 2 statements act on 2 arrays: one has 12 sets of staff initials housed in 12 separate cells and directly below is the data array of shifts being worked containing M,A,N,O representing morning, afternoon, night and off.

The first statement extracts the first occurrence of “M”, and the second finds the next “M”then selects the corresponding initials of the person working those 2 shifts.
A sample of the array could be:

Initials Array…………….BC DE FG H J K LPQ (I’ve just used single letters for simplicity)
Data Array……………….AAOOMMOOOONN
So in this example, I would be looking for a solution of F and G.

Now what I want to achieve is to extract all the “O”s, so in this case the solution would be D,E,H,J,K and L, but the new arrays will have 18 sets of initials, and the data array will have 18 shifts. Could someone suggest how the statements could be updataed/added to accommodate the changes. Hope I’ve explained this clearly enough.

Many Thanks

 

[SkipVought]

Hi,

Please use TGML Tags. It makes lining up data easier to see. If you don't know what TGML Tags are, then do a FIND in this page and line and open the link. Look for the TT Tag.

Your previous request was to return only TWO values in the array. Now you want to return SIX, if I understand you correctly. You would be better off transforming your array to COLUMNS rather than ROWS as it is now, and using MS Query to return values based on your criteria. You could get 2, 22, 2,222 22,222 or more, without any formulas AND in a relatively short period of time, like seconds.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shytott]

Hi Skip
Thanks for the tip on TGML Tags - I've been wondering how thats done.

Just for backgournd, my spreadsheet has a matrix of 365 rows (covering 1 year) and 18 cols covering all staff working the shift rota. All I am doing is extracting each row as needed, and displaying the data in a series of "windows" in a frozen panel at the top of the sheet so as I scroll down the matrix, the values (ie staff initials)in the windows change (it just makes reading it easier).

From your comments, it seems that what Im asking may not be so easy. If I were to compromise on my original idea, would it be possible to double the exisiting statements to find, the first occurrence, then the 2nd, 3rd and 4th? So the new data would be:

[tt] A B C D E F [/TT]
[TT] 1 AD BJ JK BI ML LD [/TT]
[TT] 2 O O M M O O [/TT]

and the expected reults would be:

AD, BJ, ML, LD


Cheers

 

[SkipVought]

You have fallen into the novice spreadsheet store-data-like-you-view-data trap.

Your data storage/report is not normalized. Consequently, you have hamstrung Excel by thwarting Excel's data-processing features. Look pretty, but is awful to manipulate, as you has experienced.

The way to store the data is something like...
[tt]
EntryDate StaffID TypeValue1 TypeValue2....
[/tt]
somce you really have disclosed nothing about your total data structure.

Then from that, you generate a REPORT similar to what you currently have as Source/Report.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shytott]

Ah, OK Skip, point taken - I'd be the first to agree with the label of novice!
As I've not come across the features you described - can you tell what specifically I can search on to read up about it?

Many Thanks

 

[SkipVought]

faq68-5829.

Excel Help on PivotTable or Lookup

Search in Tek-Tips.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shytott]

Thanks Skip, much obliged