need a formula may a key to a chart

[KEVINSBOOKS]

i have got a fastpitch softball team that i have rigged up a system to make calls on the field.

31 32 33 34 35 36 37 38
1 C1 CSS o2 CSS O4 C>3 NT c>2
2 c>2 C1 NT NT O4 CSS CSS O4
3 DB DB c>2 NT o2 NT C>3 CSS
4 NT C>3 O4 C>3 o2 C>3 o2 DB
5 o2 O4 O4 c>2 CSS DB NT o2
6 C1 CSS o2 c>2 NT O4 C1 C1

i would like some way to make a key for it for example
there are 8 possible calls generated randomly. C1, CSS o2, etc
i need the formula to tell me where the sign is located.

this is the example of result that i want it to generate. i want it to tell me the intersecting row and column (these numbers are NOT column numbers, they are numbers generated randomly by the program.
c1 = 311 316 322 376 386 (there could be up to 10 occurences for each call)
c>2 = 312 333 345 346 381

and so on for each of the 8 signs. i have racked my brain and exhausted my limited knowledge of excel and cant come up with it.

thanks for any help.

kevin

[ajryan88]

Hi,

Please take a look at the attachment and let me know if this is the sort of thing you're looking for?

Thanks

[KEVINSBOOKS]

ITS GREAT!!! but i pride myself on being able to work thru stuff and figure stuff out, but this is above my paygrade! i was trying to copy/move the formula so i could make a key for the whole block but after 90 minutes of fooling with it and researching the formula you made up i still cant get it to work for the other values. would you mind too terribly to help me the rest of the way?

i have been trying to solve this mystery for my girls softball team all season, and we are going to our world series this week and it would reduce my stress level a notch or two and get it off my mind. i had been just simply writing a key out by hand when i printed the sheets for the girls, but i knew there was a easier, better, and neater way to get it done.

[ajryan88]

Yeah no problems, would you mind uploading your workbook?

[KEVINSBOOKS]

done...thanks again

[ajryan88]

No problems.

I can think of 3 reasons why you couldn't get the formula to work:
1. You weren't entering it as an array formula (using Ctrl + Shift + Enter, instead of just Enter). Notice in my original formula the curly braces {} around the formula in the formula bar for any of the orange cells?
2. You weren't translating the formula to your new workbook accurately
3. Although your profile says you are using Office 2010, the files you have uploaded are in fact Excel 2003 or older files. Note that the

Please Login or Register  to view this content.

function in my formula won't work in Excel versions older than 2007. It needs to be replaced with

Please Login or Register  to view this content.

I have now got the formula working in your workbooks, but it wasn't clear where you wanted the formula so I just placed them on the right-hand side of the spreadsheet.

I hope this helps

If this solves your problem, please don't forget to mark this thread as solved (Thread Tools --> Mark As Solved), and please click on the * next to my post to say thanks