sorting and matching data, then giving a corresponding output

gojiberi

New Member
Joined
Sep 23, 2008
Messages
8
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,539
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:
Code:
=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

New Member
Joined
Sep 23, 2008
Messages
8
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,539
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top