[FONT=Arial, sans-serif]Hello Excel Community, [/FONT]
[FONT=Arial, sans-serif]
I've been trying to find a solution to my task: I want to create a function that allows excel to determine if there is a match between cells and I want it to perform the following if there is a match or not. I have included an excel sheet with an example problem and a word document describing how I imagine the function to proceed.
1. I want the Top 3 holdings (in the black border) in 1/1/2000 to see if there is any match within the Top 5 holdings in 6/1/2000. So for instance, if there is a match, I want the same matching value to be printed into the first position of the Top 3 holdings. So for C10, there will be a matching value from the Top 3 in 1/1/2000 with a Top 5 holding, so starting with C2 it will perform a scan and see that in B13, there is a match, so "SPSE" will be printed into the first 'Top Holding 3' position in C10.
Then it will continue to C3, where "SPSF" is then found to have a match in B14, so "SPSF" will be printed into cell C11 of the second 'Top holding 3' position.
As for C4, "SPSM" is not found in the Top 5 on 6/1/2000, so in this case I want it to be left blank.
2. Now since I have blank cell in the "Top holding 3" in 6/1/2000, I want it to be filled in with the next top position under the "Top 5" category on 6/1/2000, in this example, it would be "SPSTS", so "SPSTS will be filled into C12 (the blank cell under Top Holding 3, on 6/1/2000) .
***In terms of filling in blank cells under the "Top Holding 3" positions, there might be an instance where the first rank under "Top 5" (6/1/2000) has already been printed under "Top Holding 3" (6/1/2000), I then want the second rank to be choosen to be filled into the blank cell. If the first AND second rank has been matched and printed already, then I want the third rank to be printed into any remaining blank cells under "Top Holding 3" (6/1/2000)
3. In the case that if there are NO MATCHES at all, then I want the top 3 ranks under "Top 5" (6/1/2000) to be selected and printed across into the blank cells of C10, C11, and C12 under "Top Holding 3" (6/1/2000)
I know this is extremely confusing (I was very confused at first when asked to do this), but I want this to be repeatable over a wide range of dates while keeping the format fixed as it is. Please check the attached excel example and the word document for further description, I would greatly appreciate any help on this as I have been stuck trying to figure something out for over a week now. I'm not sure if using a nested IF statement would be a good solution or to create a macro (which I have little experience in doing something this complex), but any solution would be fine! I hope someone can help me with this.
Thank you in advance!
Links to example and description:
Example:
http://www.mediafire.com/?di0rr94qaw9939t
Description (word doc):
http://www.mediafire.com/?iec40703msw02ko
[/FONT]
[FONT=Arial, sans-serif]
I've been trying to find a solution to my task: I want to create a function that allows excel to determine if there is a match between cells and I want it to perform the following if there is a match or not. I have included an excel sheet with an example problem and a word document describing how I imagine the function to proceed.
1. I want the Top 3 holdings (in the black border) in 1/1/2000 to see if there is any match within the Top 5 holdings in 6/1/2000. So for instance, if there is a match, I want the same matching value to be printed into the first position of the Top 3 holdings. So for C10, there will be a matching value from the Top 3 in 1/1/2000 with a Top 5 holding, so starting with C2 it will perform a scan and see that in B13, there is a match, so "SPSE" will be printed into the first 'Top Holding 3' position in C10.
Then it will continue to C3, where "SPSF" is then found to have a match in B14, so "SPSF" will be printed into cell C11 of the second 'Top holding 3' position.
As for C4, "SPSM" is not found in the Top 5 on 6/1/2000, so in this case I want it to be left blank.
2. Now since I have blank cell in the "Top holding 3" in 6/1/2000, I want it to be filled in with the next top position under the "Top 5" category on 6/1/2000, in this example, it would be "SPSTS", so "SPSTS will be filled into C12 (the blank cell under Top Holding 3, on 6/1/2000) .
***In terms of filling in blank cells under the "Top Holding 3" positions, there might be an instance where the first rank under "Top 5" (6/1/2000) has already been printed under "Top Holding 3" (6/1/2000), I then want the second rank to be choosen to be filled into the blank cell. If the first AND second rank has been matched and printed already, then I want the third rank to be printed into any remaining blank cells under "Top Holding 3" (6/1/2000)
3. In the case that if there are NO MATCHES at all, then I want the top 3 ranks under "Top 5" (6/1/2000) to be selected and printed across into the blank cells of C10, C11, and C12 under "Top Holding 3" (6/1/2000)
I know this is extremely confusing (I was very confused at first when asked to do this), but I want this to be repeatable over a wide range of dates while keeping the format fixed as it is. Please check the attached excel example and the word document for further description, I would greatly appreciate any help on this as I have been stuck trying to figure something out for over a week now. I'm not sure if using a nested IF statement would be a good solution or to create a macro (which I have little experience in doing something this complex), but any solution would be fine! I hope someone can help me with this.
Thank you in advance!
Links to example and description:
Example:
http://www.mediafire.com/?di0rr94qaw9939t
Description (word doc):
http://www.mediafire.com/?iec40703msw02ko
[/FONT]
