sorting and matching data, then giving a corresponding output

[gojiberi]

I have a array of data that import from a sports analysis package, in the form of letters and numbers, e.g. A1, BC20, DD31.

I want to search through this array of data (in rows) and find particular combinations, for example, any within these ranges DA23-DA26, DA31-DA34, DA39-DA42 (these ranges are repeated for DB, DC, and DD).

If any of these "D" data combinations are found in the table array, it is referring to a particular outcome in the game.
All of the 23-26 for DA, DB, DC and DD ranges means that the outcome was a Turnover.
All of the 31-34 for DA, DB, DC and DD ranges means that the outcome was Successful.
All of the 39-42 for DA, DB, DC and DD ranges means that the outcome was Unsuccessful.

In the end I need to check each row of data within the array to see what the outcome was, and then come up with a tally of outcomes for turnover, successful and unsuccessful.

I have been reading the match, hlookup, index etc functions, but am not sure which combination of functions I can use to get the information I want.

Any help will be greatly appreciated.... i am trying to avoid using macros...

thanks,
gojiberi

 

[GlennUK]

You could have a table of:
DA23 Turnover
DA24 Turnover
etc etc for all the entries to be matched. Then it would be a simple INDEX MATCH lookup, in the column next to the original data, like this:

=IF(ISNA(MATCH(A1,MyTable_Left_Col,0)),"",INDEX(MyTable_Right_Col,MATCH(A1,MyTable_Left_Col,0)))

The you can do a pivottable of your data area and formulas to tally what's what.

 

[gojiberi]

I may not have explained what I need very clearly.

I might just start with the first step:

I have a row of data, say F3:AS3, I need to find out which of the following values below are present in data F3:AS3:

DA23
DA24
DA25
DA26
DB23
DB24
DB25
DB26
DC23
DC24
DC25
DC26
DD23
DD24
DD25
DD26

If its DA##, the result is turnover, if its DB## the result is Shot Successful and if its DC# the result is Shot Unsuccessful.

In addition to the F3:AS3 row of data I also need to search 40 other rows of data for the same outcomes.

In the end I would like a total of how many turnovers there were, how many shots successful and how many shots unsuccessful.

thanks ahead of time for your help!
gojiberi

 

[GlennUK]

I had no trouble understanding your query. But you seem to be having trouble understanding my suggestion ... using INDEX and MATCH from a table of values will give you exactly what you want ( i.e. convert the DA## and DB## into Turnover/Successful/Unsuccessful ), and a pivottable of those results will give you the counts you want.