L
Lee Harris
I fear this will involve some array functions or worse, but it's worth
asking because it will save me loads and loads of time if I can do it neatly
I have a list of items and various columns, one of which is a calculation
that gives a number on which everything is based, let's call it "score1",
there is then a second value called "Score2"
Score1 cannot be outside the limits +10 to +14, in steps of 0.1
Score2 cannot be outside the limits -5 to +2, in steps of 0.1
Based on "Score1" and "Score2", there are two lookups that i want to
converge
the first (using Score1) looks in a table and produces an outcome of the
following format,
e.g
Table 1
10, SSSSLLLL
10.1, SSSLSLLL
etc - i.e different combinations of S (for secondary) and L (for
linebackers)
the second (using Score2) looks in a different table, with an outcome like
-5 SF,SF,CB,CB (this is a single string with commas)
-4.9 SF,CB,SF,CB
etc
The problem I have is this gives me two strings which I then have to
manually correct into a single string
eg, if Score1 is 10 (SSSSLLLL) and Score2 is -4.9 (SS,CB,FS,CB)
the current sheet just shows both results in a different column
I must then manually combine into SF1/CB1/SF2/CB2/LB1/LB2/LB3/LB4
(what it does is replace all the "S" characters with secondary - but in the
order specified by the second string, and replace all the "L" with "LB", and
the numbers show the order of each player within the string, so there are 2
Cornerbacks, 2 Safeties and 4 linebackers)
It's a trivial matter to then double the string by concatenation, changing
all the number 1,2,3,4 into 5,6,7,8 to end up with a string of 16 tokens in
the appropriate "type" order specified by the 1st string, in the specific
secondary order defined by string two, with LBs numbered in order wherever
the L's appear
What I tried to do is have some kind of indexed lookup but that only works
for a single table example. It's the fact that there are two results which
must be combined that's got me stumped.
I can send a small example sheet to anyone who wants to see the real data in
action, I would be thrilled if I could get this to work
(the very very ugly "solution" consists of making a 2800 row table and
indexing into it by multiplying Score 1 and Score 2, and hand typing out the
combined solutions per each row!)
asking because it will save me loads and loads of time if I can do it neatly
I have a list of items and various columns, one of which is a calculation
that gives a number on which everything is based, let's call it "score1",
there is then a second value called "Score2"
Score1 cannot be outside the limits +10 to +14, in steps of 0.1
Score2 cannot be outside the limits -5 to +2, in steps of 0.1
Based on "Score1" and "Score2", there are two lookups that i want to
converge
the first (using Score1) looks in a table and produces an outcome of the
following format,
e.g
Table 1
10, SSSSLLLL
10.1, SSSLSLLL
etc - i.e different combinations of S (for secondary) and L (for
linebackers)
the second (using Score2) looks in a different table, with an outcome like
-5 SF,SF,CB,CB (this is a single string with commas)
-4.9 SF,CB,SF,CB
etc
The problem I have is this gives me two strings which I then have to
manually correct into a single string
eg, if Score1 is 10 (SSSSLLLL) and Score2 is -4.9 (SS,CB,FS,CB)
the current sheet just shows both results in a different column
I must then manually combine into SF1/CB1/SF2/CB2/LB1/LB2/LB3/LB4
(what it does is replace all the "S" characters with secondary - but in the
order specified by the second string, and replace all the "L" with "LB", and
the numbers show the order of each player within the string, so there are 2
Cornerbacks, 2 Safeties and 4 linebackers)
It's a trivial matter to then double the string by concatenation, changing
all the number 1,2,3,4 into 5,6,7,8 to end up with a string of 16 tokens in
the appropriate "type" order specified by the 1st string, in the specific
secondary order defined by string two, with LBs numbered in order wherever
the L's appear
What I tried to do is have some kind of indexed lookup but that only works
for a single table example. It's the fact that there are two results which
must be combined that's got me stumped.
I can send a small example sheet to anyone who wants to see the real data in
action, I would be thrilled if I could get this to work
(the very very ugly "solution" consists of making a 2800 row table and
indexing into it by multiplying Score 1 and Score 2, and hand typing out the
combined solutions per each row!)