Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
I'm still fairly new at excel. I've gotten pretty good at formulas though, and just starting VBA. I thought I would share a process I've worked out for developing complex formulas. This is gonna be pretty long winded, I hope this is the right place for this.
1st thing is to have it clear in your head what you want. And maybe even physically write out in Plain english what you want. Then take very small steps. Write each part of the formula seperately as it's own simple formula. Starting with the simplest, most basic function you want accomplished. Then you can combine those small simple formulas, into one larger more complex formula. I'm going to walk through my thought process developing a pretty complex formula (for me anyway). This is just an example, not every situation will work exactly the same way. But the basic idea is to start with small simple formulas, then combine them into 1 big formula.
Here's The end result formula I came up with :
Now, my first step was deciding what I wanted. I have a sheet of data with IDs in collumn A. Then collumns B - K have data related to the ID. Collumn K is a Comments field. What I wanted to do was return the comments based on the ID# to another sheet. But I only wanted a PART of the comments. In this case, everything that came after the first (. Not all comments have a ( in it, and I did NOT want those comments returned. The ( can be in random places within the comments. I also want to NOT display the #N/A if the ID# is not found in the table, or Value if it's blank, or there is no (.
It was a pretty daunting task for me as a beginner (maybe intermediate now) excel user.
Again, simple steps. I began by creating a new blank sheet to develop formula with, and when it's done, then I put the formula in the actual sheet I want it in. Then created small formulas (each in it's own cell) for each small function that needed to be accomplished. Then slowly combined each of those small formulas into one.
Here we go.
The vlookup function of coarse is the simplest step.
so in A1 on my blank sheet, I entered an ID# to look for. In b1, I entered your basic vlookup formula.
Now still going by easiest step first...How to eliminate errors? #N/A, Value etc..
in a new cell, c1, I entered
But I want the iserror to return Blank if there is an error, and return "NO Error" If there was no error,Instead of the standard True/False results. ...I should be able to use an IF for that. so in d1 I put
I made it return "Error" if there is an error and "No Error" if there isn't an error for testing purposes, so I can verify the formula works. I can correct that later.
Now I can combine the Iserror and the IF formulas into one, I can litterally copy the iserror fomrula into the If formula. so I copy the formula in C1 (minus the = sign), press esc, go to d1, highlight the reference C1, and paste. D1 now looks like
What I really wanted was the iserror to do the vlookup if there was no error. So I have the vlookup done already in b1. So I can just put a reference to B1 where it says "No Error". So D1 now looks like
Since the Iserror function is now in D1, I no longer need the formula in C1, So I deleted collumn C
Now what was in D1 is now in C1
Great, Now I have C1 returning the vlookup results only if the vlookup does not result in an error. If the vlookup does result in an error, it returns "Error". Almost there. But I don't want to combine the Vlookup formula from B1 into the formula in C1 yet, because the vlookup isn't complete yet. It still returns the entire contents of comments field in collumn K sheet1. I only want part of it.
Now how to make the vlookup return only Part of the Comments in column K?
Find something common in the text you want to return that is ALWAYS there. In my case, it's the "(".
so again start simple. I need to find the ( in the text. I already have returned the text to b1. the find function should work for that. so I put in d1
That returns the # of the first character that contains a "(" in the text B1 looked up.
But I want to return what is to the right of the (. I can use the Right function for that, but that function needs to know How Many Characters from the end of the text string you want to return. Since these text strings vary in length, I'll need to calculate the difference between the result of D1, and the length of the text string. First I need the length of the text string, I can use Len for that. So in E1 I entered
That returns the # of characters in the whole text string.
Now, to get the # of characters there are from the ( to the end, I need to subtract D1 from E1.
so in F1 I enter :
OK, now I have all the information the RIGHT finction needs to get the text I want after the first (. Where the text is, and How many characters from the end of the text string.
Written in plain english, I need text on the Right side of the first (. Then sudo english code = right(where to look, # of characters) = right(b1,f1)
So in G1 :
Excellent, I've done everything that needs to be done to get the results I wanted. In 6 different formulas.
Since I now have the results I want in G1, I could just stop here and hide collumns B-F. This is a personal judgement call for everone to make. Drawing the line between having several cells with simple formulas, or having super long complex formulas in 1 cell. Me personally, I like to have as few formulas as possible, even if they are extremely long. It becomes a headache for me to keep track of which collum is doing what if I ever need to make adjustments. So I prefer to combine the small formulas into 1 large formula. So now we continue..
Now I need to combine the 6 formulas in B1 - G1 into 1 formula.
Start slow, combine 2 into 1 to make 5. Then another 2 into 1 to make 4, etc...This is the part that will vary for each situation you are developing formulas for. You just have to go slowly, and think logically. It would be a good idea to save your worksheet at this point, in case you mess up. Sometimes this takes some trial and error.
I can litterally copy/paste formulas from once cell (minus the =) into a formula (replacing the reference to the cell I copied it from.)
I'm going to start by combining the Vlookup, into the Right Function. Copy B1 (without the = sign), press esc. go to g1, replace Hightlight B1, Paste.
G1 now looks like this :
this still refers to F1, so Now I can repeat, copy contents of F1 (minus = sign), and paste into G1 in place of F1.
G1 looks like this :
Repeat, copy E1 contents to replace E1
copy D1 contents to replace D1
Now I have two references to to B1 there, that came from the Len and Find formulas.
copy contents of B1, replace reference in G1 to B1 twice.
Just about done, Now the formula does everything, looks up the ID in A1 in the table on sheet 2 A:K. Returns the Comments In K (with ONLY the text to the right of the first (.
Excellent. But now I've just got to get the Iserror funciton back in there.
I had that in C1, right....
C1 looks like this still.
But there is no reference in G1 to C1. Nothing to replace. So this time I will reverse the process, and paste the contents of G1 into C1 replacing B1 twice, because b1 was the original vlookup, I want the corrected vlookup in there now and I'll be done.
There are 2 references to B1 in the formula in C1, so I've got to do it twice.
First time :
Now there is just 1 reference left to B1
do it again for the 2nd B1 reference :
Final adjustment is that I Put "Error" in there if the Iserror function returned TRUE, just for testing purposes to verify it worked. I really want it to return blank if there is an error. So just remove the word error. And you get the formula at the beginning of this post.
That's it. It is done.
1st thing is to have it clear in your head what you want. And maybe even physically write out in Plain english what you want. Then take very small steps. Write each part of the formula seperately as it's own simple formula. Starting with the simplest, most basic function you want accomplished. Then you can combine those small simple formulas, into one larger more complex formula. I'm going to walk through my thought process developing a pretty complex formula (for me anyway). This is just an example, not every situation will work exactly the same way. But the basic idea is to start with small simple formulas, then combine them into 1 big formula.
Here's The end result formula I came up with :
Code:
=IF(ISERROR(RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE))-FIND("(",VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),1)))=TRUE,"",RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE))-FIND("(",VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),1)))Now, my first step was deciding what I wanted. I have a sheet of data with IDs in collumn A. Then collumns B - K have data related to the ID. Collumn K is a Comments field. What I wanted to do was return the comments based on the ID# to another sheet. But I only wanted a PART of the comments. In this case, everything that came after the first (. Not all comments have a ( in it, and I did NOT want those comments returned. The ( can be in random places within the comments. I also want to NOT display the #N/A if the ID# is not found in the table, or Value if it's blank, or there is no (.
It was a pretty daunting task for me as a beginner (maybe intermediate now) excel user.
Again, simple steps. I began by creating a new blank sheet to develop formula with, and when it's done, then I put the formula in the actual sheet I want it in. Then created small formulas (each in it's own cell) for each small function that needed to be accomplished. Then slowly combined each of those small formulas into one.
Here we go.
The vlookup function of coarse is the simplest step.
so in A1 on my blank sheet, I entered an ID# to look for. In b1, I entered your basic vlookup formula.
Code:
=VLOOKUP(A1,Sheet1!$A:$K,11,FALSE)Now still going by easiest step first...How to eliminate errors? #N/A, Value etc..
in a new cell, c1, I entered
Code:
=ISERROR(B1)But I want the iserror to return Blank if there is an error, and return "NO Error" If there was no error,Instead of the standard True/False results. ...I should be able to use an IF for that. so in d1 I put
Code:
=if(c1=TRUE,"Error","No Error")I made it return "Error" if there is an error and "No Error" if there isn't an error for testing purposes, so I can verify the formula works. I can correct that later.
Now I can combine the Iserror and the IF formulas into one, I can litterally copy the iserror fomrula into the If formula. so I copy the formula in C1 (minus the = sign), press esc, go to d1, highlight the reference C1, and paste. D1 now looks like
Code:
=IF(ISERROR(B1)=TRUE,"Error","No Error")What I really wanted was the iserror to do the vlookup if there was no error. So I have the vlookup done already in b1. So I can just put a reference to B1 where it says "No Error". So D1 now looks like
Code:
=IF(ISERROR(B1)=TRUE,"Error",B1)Since the Iserror function is now in D1, I no longer need the formula in C1, So I deleted collumn C
Now what was in D1 is now in C1
Code:
=IF(ISERROR(B1)=TRUE,"Error",B1)Great, Now I have C1 returning the vlookup results only if the vlookup does not result in an error. If the vlookup does result in an error, it returns "Error". Almost there. But I don't want to combine the Vlookup formula from B1 into the formula in C1 yet, because the vlookup isn't complete yet. It still returns the entire contents of comments field in collumn K sheet1. I only want part of it.
Now how to make the vlookup return only Part of the Comments in column K?
Find something common in the text you want to return that is ALWAYS there. In my case, it's the "(".
so again start simple. I need to find the ( in the text. I already have returned the text to b1. the find function should work for that. so I put in d1
Code:
=FIND("(",B1,1)That returns the # of the first character that contains a "(" in the text B1 looked up.
But I want to return what is to the right of the (. I can use the Right function for that, but that function needs to know How Many Characters from the end of the text string you want to return. Since these text strings vary in length, I'll need to calculate the difference between the result of D1, and the length of the text string. First I need the length of the text string, I can use Len for that. So in E1 I entered
Code:
=LEN(B1)That returns the # of characters in the whole text string.
Now, to get the # of characters there are from the ( to the end, I need to subtract D1 from E1.
so in F1 I enter :
Code:
=E1-D1OK, now I have all the information the RIGHT finction needs to get the text I want after the first (. Where the text is, and How many characters from the end of the text string.
Written in plain english, I need text on the Right side of the first (. Then sudo english code = right(where to look, # of characters) = right(b1,f1)
So in G1 :
Code:
=RIGHT(B1,F1)Excellent, I've done everything that needs to be done to get the results I wanted. In 6 different formulas.
Since I now have the results I want in G1, I could just stop here and hide collumns B-F. This is a personal judgement call for everone to make. Drawing the line between having several cells with simple formulas, or having super long complex formulas in 1 cell. Me personally, I like to have as few formulas as possible, even if they are extremely long. It becomes a headache for me to keep track of which collum is doing what if I ever need to make adjustments. So I prefer to combine the small formulas into 1 large formula. So now we continue..
Now I need to combine the 6 formulas in B1 - G1 into 1 formula.
Start slow, combine 2 into 1 to make 5. Then another 2 into 1 to make 4, etc...This is the part that will vary for each situation you are developing formulas for. You just have to go slowly, and think logically. It would be a good idea to save your worksheet at this point, in case you mess up. Sometimes this takes some trial and error.
I can litterally copy/paste formulas from once cell (minus the =) into a formula (replacing the reference to the cell I copied it from.)
I'm going to start by combining the Vlookup, into the Right Function. Copy B1 (without the = sign), press esc. go to g1, replace Hightlight B1, Paste.
G1 now looks like this :
Code:
=RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),F1)this still refers to F1, so Now I can repeat, copy contents of F1 (minus = sign), and paste into G1 in place of F1.
G1 looks like this :
Code:
=RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),E1-D1)Repeat, copy E1 contents to replace E1
Code:
=RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(B1)-D1)copy D1 contents to replace D1
Code:
RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(B1)-FIND("(",B1,1))Now I have two references to to B1 there, that came from the Len and Find formulas.
copy contents of B1, replace reference in G1 to B1 twice.
Code:
=RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE))-FIND("(",VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),1))Just about done, Now the formula does everything, looks up the ID in A1 in the table on sheet 2 A:K. Returns the Comments In K (with ONLY the text to the right of the first (.
Excellent. But now I've just got to get the Iserror funciton back in there.
I had that in C1, right....
C1 looks like this still.
Code:
=IF(ISERROR(B1)=TRUE,"Error",B1)But there is no reference in G1 to C1. Nothing to replace. So this time I will reverse the process, and paste the contents of G1 into C1 replacing B1 twice, because b1 was the original vlookup, I want the corrected vlookup in there now and I'll be done.
There are 2 references to B1 in the formula in C1, so I've got to do it twice.
First time :
Code:
=IF(ISERROR(RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE))-FIND("(",VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),1)))=TRUE,"Error",B1)Now there is just 1 reference left to B1
do it again for the 2nd B1 reference :
Code:
=IF(ISERROR(RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE))-FIND("(",VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),1)))=TRUE,"Error",RIGHT(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),LEN(VLOOKUP(A1,Sheet1!$A:$K,11,FALSE))-FIND("(",VLOOKUP(A1,Sheet1!$A:$K,11,FALSE),1)))Final adjustment is that I Put "Error" in there if the Iserror function returned TRUE, just for testing purposes to verify it worked. I really want it to return blank if there is an error. So just remove the word error. And you get the formula at the beginning of this post.
That's it. It is done.
