Excel - Extracting Data 1

[shytott]

Hi
Could any one show me if its possible to extract 2 cells of data from a row of 12 cells. In the example below, fisrt line is Col Number, next is staff initials, and next two are examples of the combinations of data (there are 365 rows in all). In these two examples, I am only interested in identifying which memebr of staff is working a "M" (morning) shift:

1 2 3 4 5 6 7 8 9 10 11 12
AD BJ HS ED TT AB SW SR KL DG GG AR
O O O N N M M A A O O O
M O O N N M A A O O O O

The result I would be looking for would then go into 2 new cols thus:
13 14
AB SW
AD AB


Many Thanks

 

[SkipVought]

Hi,

Why VBA?
[tt]
M3: =INDEX($A$2:$L$2,1,MATCH("M",$A3:$L3,0))
N3: =INDEX($A$2:$L$2,1,MATCH("M",OFFSET($A3,0,MATCH("M",$A3:$L3,0),1,12),0)+MATCH("M",$A3:$L3,0))[/v]
[/tt]
My results...
[tt]
AB SW
AD AB
[/tt]


Skip,

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

 

[shytott]

Hi Skip
Thanks for your reply. I have set up a new blank worksheet exactly as I had in my post, ie the fisrt 'AD' is in cell A1, then I placed your two functions in M3 and N3 respectively and got O and M as the result - no initials appeared.

Im not sure what the and the start of N3 and [/v] at the end signify, but I had to remove them to stop Excel saying there was an error.
Also, is there a reason why your functions have to go in Row 3? I will only ever be looking at one row of data at any one time, I just used 2 lines here to show 2 possible different scenarios. I tried copying your funcs to M2 and N2 and got M and M as the result.
By the way, what are these constructs called? Its a new one on me!

Many Thanks
AD

 

[SkipVought]

In the example below, fisrt line is Col Number, next is staff initials, and next two are examples of the combinations of data (there are 365 rows in all).

The sheet EXACTLY as you posted are FOUR rows of data.
[tt]
1 2 3 4 5 6 7 8 9 10 11 12
AD BJ HS ED TT AB SW SR KL DG GG AR
O O O N N M M A A O O O
M O O N N M A A O O O O
[/tt]
If you intended THREE rows of data, then you should have EXPLICITLY stated that the first line only represents the columns and is NOT data.

Yes, disregard the [bracked] data. I did not code the TGML tags correctly to BOLD the SECOND formula.

Here is how to "FIX" the formulas.

1. insert an EMPTY row over in ROW 1

2. paste the two formulas in the cells indicated

3. DELETE Row 1.

Skip,

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

 

[shytott]

Hi Skip
Sorry about the lack of clarity re the Col Numbers - fair point, noted for next time.
And the good news is, that I have got it to work. Thanks very much (again).

Can you tell me what these constructs / functions are called so I can read up about them?

Cheers
AD

 

[SkipVought]

INDEX, MATCH and OFFSET are lookup & reference functions.



Skip,

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

 

[shytott]

Thanks Again

 

[shytott]

Skip
Harking back to your solution, I can get it to work perfectly on a brand new spreadsheet with AD in cell A1. This works for any permutation of ‘M’s whether they be next door to each other or 4 cells apart – and this is the exact functionality I’m after. However, when I transpose this to my actual spread sheet (which does not have the Initials and data starting at A1), the first referencing works (ie for your ‘M2’), but N2 will only work if it is the next ‘M’ on the data sequence. If it is 2 or more apart, it fails showing #N/A.

The actual cell addresses on the working spreadsheet are:

Initials run K3:V3
Data runs K4:V4

Result 1 to be displayed in F8
Result 2 to be displayed in I8

ie the obvious difference being the solution cells are not next to each other and I imagine this is the reason for the failure. Ive tried reading up on INDEX, MATCH and OFFSET but it hasnt suggested anything to me as yet.

Sorry I wasn’t able to provide this on my initial post as I was doing this from memory at home and the actual spreadsheet is at my job 200 miles away!

Hope this is not too much of a drain on your patience!

Many Thanks

AD

 

[SkipVought]

if the DATA structure is different, 1) please post an example, in addition to the prose.

2) Point out exactly where the problems seems to be occuring, and 3) what you expect the results to be.

Skip,

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

 

[shytott]

Skip

Ok, the data and structure are the same as we dealt with previously, 12 sets of staff initials in separate cells and directly underneath is the changing data that I am reading in consisting of M’s, A’s, N’s or Os (I am not classifying the Initials as data). Each line of data is going to have 2 M’s, 2A’s and 2 N’s contained some where with in it and they could be any where with in that’s 12 cell range. I need to be able to extract the 3 pairings and display the 6 sets of initials in 6 separate cells (these would be the M2 and N2 of your original solution which was only searching out the ‘M’s ( I will then alter the formulae to extract the A’s and N’s.


Ie for:

AD BJ HS ED TT AB SW SR KL DG GG AR
O O M M O A A O O N N O

I would expect to see :
Cell F8: HS
Cell I8: ED

And for

AD BJ HS ED TT AB SW SR KL DG GG AR
O O O M O A A O M N N O


I would expect to see :
Cell F8: ED
Cell I8: KL


With regard to when the transposition to my working spreadsheet was failing:
With reference to the 12 cells of data, two neighboring ‘M’s no matter where will work. If they have one or more cells between them, it fails ie position 2 and 4, or 2 and 8, or 4 and 11 etc.

Thanks
AD

 

[SkipVought]

What ROWS/COLUMNS is this data in

Skip,

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

 

[shytott]

Initials run K3:V3
Data runs K4:V4

 

[SkipVought]

[tt]
F8: =INDEX(H3:S3,1,MATCH("M",H4:S4,0))
i8: =INDEX(H3:S3,1,MATCH("M",OFFSET(H4,0,MATCH("M",H4:S4,0),1,12),0)+MATCH("M",H4:S4,0))
[/tt]
If you put the CHARACTER that you want to search on in row 7 in the cells above ...
[tt]
F8: =INDEX(H3:S3,1,MATCH(F7,H4:S4,0))
I8: =INDEX(H3:S3,1,MATCH(I7,OFFSET(H4,0,MATCH(I7,H4:S4,0),1,12),0)+MATCH(I7,H4:S4,0))
[/tt]


Skip,

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

 

[shytott]

Brilliant, youre a genius!

Thanks for your second idea, but what I have are 2 'windows' to display the M (morning ) shifts, another 2 for the A's and a final 2 for the N's which is what I need to display every day (ie for the line of 12 data items of which there are 365 in all). Its a shame we cant post screen prints - it would have helped me explain my plight!

But we got there, and thanks again for your patience!

Cheers
AD

 

[SkipVought]

HINT: If you EDIT a formula with references on the same sheet, you can SEE the referenced cell(s) outlined in the same COLORS as the refereces in the formula.

To correct the formula, highlight the appropriate reference in the formula and then select the cell(s) that it should reference.

Skip,

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

 

[shytott]

Yup
Spotted that, and Ive just transposed the formulae to the other A and N windows succssessfully - also noticed that you can drag a coloured range to a new location if need be.

Its always a learning curve