Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Converging two lookups? (would save me AEONS of time!)
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!) |
#2
|
|||
|
|||
Converging two lookups? (would save me AEONS of time!)
"Lee Harris" wrote in message ... 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!) Actually I found a rather easy solution by making a 2D table, just required me to tweak 30 rows of concatenations |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
Deducting breaks from time measurements | Excel Worksheet Functions | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Time Required to Save a File | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |