Extracting Data using Index or Match?

**Shytott** · 12-18-2009, 01:36 AM

Hi
I have rows of 18 items of data comprised of "M", "A", "N" and "O"s from which I need to identify one set of "M" "A" "N"s and extract the relevant Col headers.

I have shown part of the main matrix of data together with the expected results on the attached spreadsheet. I have tried to achieve what Im after by using index and match but I couldnt get it to work, there again this might be totally the wrong approach, so I'd be grateful for any suggestions.

Many Thanks
Shytott

**contaminated** · 12-18-2009, 01:56 AM

what criterion did u use to highlight columns?
Say plz what mean these colors????

**zbor** · 12-18-2009, 03:15 AM

First of all, why you using middle M in first group, last M in seocnd group and first M in thrid group?

(samo for AN)

**teylyn** · 12-18-2009, 04:10 AM

You see from the comments above that your logic for arriving at the mocked up results is not clear at all. Please provide a whole set of rules together with your mock-up.

cheers

**contaminated** · 12-18-2009, 05:14 AM

I think it's been done......

Please Login or Register  to view this content.

**Shytott** · 12-19-2009, 09:21 PM

Sorry for the delay in replying and also for not describing more clearly what’s going on. To clarify:
The spreadsheet is managing 6 teams of 3 engineers working a 24/7 shift rota consisting of either Mornings, Afternoons, Nights or Off. This is a repeating 6 week pattern throughout the year. I have shown the entire extent of the 3 x 6 week cycles (ie 18 weeks in all) in the new spreadsheet attached.

On top of this, the 3 Cols to the right (AJ – AL) show which engineer is on Standby. Standby is manned by 3 teams derived from the 6 main teams, ie
Standby team 1 (orange) is the 1st man in each of the 6 teams,
Standby team 2 (green) is the 2nd man in each of the 6 teams,
Standby team 3 (blue) is the 3rd man in each of the 6 teams,

I have tried ContaminatedWitExcels solution which works to some extent, but if eg the man working N is set to Off, then the corresponding "N" col in Standby - (Col AL) needs to be blank - as no one is shown as working N in the matrix) but the formula is picking up DB which is incorrect.

The aim of my query here is to pick out and highlight any changes that have been made to the matrix / Standby (for holiday/sickness etc). I intend to do this by comparing the ‘working’ copy of the matrix and standby (ie what you are seeing in this spreadsheet)with an un altered master.

Cheers
Shytott