Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Morning again folks.
Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCH(E5,SUMMARY!$A$10:$A$60,0 ))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
You will get #N/A with Roger's formula if there is no exact match, and
a blank cell if E5 is blank. With mine you will get a blank cell if there is no match (I don't check for E5 being empty). Actually, your range in the second formula goes to B82 instead of B60, but that doesn.t really affect things. So, what the results are telling you is that there is no exact match. This implies that there is something different between the value in E5 and the values in column A. If these are text values that look the same, then perhaps you have extra spaces in E5 or column A so there is no exact match. If they are meant to be numeric values, then perhaps one set of data is numeric and the other is actually text that happens to look like it's numeric. I would suggest that you examine your data more carefully. You can use =LEN(cell) to find out how many characters are in the cell if they are text strings. You can use =ISNUMBER(cell) to check if the cell contains a number. Hope this helps. Pete On Feb 3, 3:52*pm, SteveDB1 wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCH*(E5,SUMMARY!$A$10:$A$60, 0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60*,2,0)) I've saved these in a notepad file so I have them at the ready for my use.. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and *other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Ok, now I'm really confused. I just tried Roger's version on two more files,
and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCH(E5,SUMMARY!$A$10:$A$60,0 ))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Steve,
our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16*pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCH*(E5,SUMMARY!$A$10:$A$60, 0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60*,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and *other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Hi again Pete. Thanks for getting back to me so quickly.
I think that I figured part of my problem out. I found that if the data type on my summary/sum sheet is a text, and the data type on my primary sheet is say, general, I have to change the data type on one sheet to match the other. And then I need to activate each cell to "force" the data type conversion. We had this same exact issue two and a half years ago with sumproduct. The solution that either Roger, or another guy who was helping provided was either a double negative, or &"" in front of, or following the row/column arrays. Would those apply here as well? I only ask because I've found that with some of my attempts at using those data-type nullifiers, they don't always resolve the problem. Again-- thank you. "Pete_UK" wrote: You will get #N/A with Roger's formula if there is no exact match, and a blank cell if E5 is blank. With mine you will get a blank cell if there is no match (I don't check for E5 being empty). Actually, your range in the second formula goes to B82 instead of B60, but that doesn.t really affect things. So, what the results are telling you is that there is no exact match. This implies that there is something different between the value in E5 and the values in column A. If these are text values that look the same, then perhaps you have extra spaces in E5 or column A so there is no exact match. If they are meant to be numeric values, then perhaps one set of data is numeric and the other is actually text that happens to look like it's numeric. I would suggest that you examine your data more carefully. You can use =LEN(cell) to find out how many characters are in the cell if they are text strings. You can use =ISNUMBER(cell) to check if the cell contains a number. Hope this helps. Pete On Feb 3, 3:52 pm, SteveDB1 wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*(E5,SUMMARY!$A$10:$A$60 ,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*,2,0)) I've saved these in a notepad file so I have them at the ready for my use.. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
yea, it looks like we were posting within minutes of each other, and the list
delay for the newsgroup was causing the overlaps. I understood/stand that if there's no value, the cell would remain blank. Which was indeed part of what I'd wanted. I also understood that Match would return an #N/A error if it did not fint an exact match. Which was why I'd thought of using it, but "mis-ordered" my version in comparing my original to yours, and Roger's. At this point, I'm thinking that it's a data-type issue, and am wondering if there is a data-type nullifier that'd resolve the issues. I am aware of --, and &"". I have had occurrences that I've tried both of those and they didn't work for my purposes-- and yes, I'd be willing to consider that there was something else that I'd missed which invalidated their affect. Again, thank you for your time. Steve "Pete_UK" wrote: Steve, our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16 pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*(E5,SUMMARY!$A$10:$A$60 ,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Hi Steve
only just picked up on this thread. It sounds as though you may have leading or trailing spaces, or, if the data has been copied from the web, then the non-breaking space Char(160). You could build in to the formula, substituting these characters with Null, but I would be more inclined to "clean" the source data. In a spar column on the source data sheet =SUBSTITUTE(SUSTITUTE(A10," ",""),Char(160),"") copy down as far as required Copy this "cleaned data" and paste Specialvalues back over the data in column A. Pete's formula does deal with case of the value not being matched (a good idea) but it could perhaps be achieved slightly more efficiently by using a single function call with Countif to check if E5 exists in column A. The following tests for Null value in E5 and No Match being found. =IF(E5="","",IF(COUNTIF(SUMMARY!$A$10:$A$60,E5), INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SUMMARY!$A$10:$ A$60,0))),"") -- Regards Roger Govier "SteveDB1" wrote in message ... yea, it looks like we were posting within minutes of each other, and the list delay for the newsgroup was causing the overlaps. I understood/stand that if there's no value, the cell would remain blank. Which was indeed part of what I'd wanted. I also understood that Match would return an #N/A error if it did not fint an exact match. Which was why I'd thought of using it, but "mis-ordered" my version in comparing my original to yours, and Roger's. At this point, I'm thinking that it's a data-type issue, and am wondering if there is a data-type nullifier that'd resolve the issues. I am aware of --, and &"". I have had occurrences that I've tried both of those and they didn't work for my purposes-- and yes, I'd be willing to consider that there was something else that I'd missed which invalidated their affect. Again, thank you for your time. Steve "Pete_UK" wrote: Steve, our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16 pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*(E5,SUMMARY!$A$10:$A$60 ,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Hi Roger,
The data was not copied from the web. One of the things I did find was, as stated, a data-type difference between the two worksheets. I don't know if you remember-- enough time has passed-- but I'd posted back in October of 06 asking about troubles I was having with Sumproduct. Harley had tried answering my posts, and we both ended up getting short with each other. At that point you'd stepped in, and helped me work through the details of my problem. Which wound up being a data-type difference. I now use &"" in all my work with sumproduct because I've found it to work the best. This morning, after I'd reposted, I decided to look at my data-types and found that my data on the Sum page was text in some cases, and in others it was general. The data on my sheet where the equation was located was mostly general, with an occasional number data-type. As I found that, I decided to change the data-type by standard means, and then go through and activate each cell, thus "forcing" the conversion. I then checked my base sheet, and all of the data I wanted was there. Hence, I'm now wondering if there's a data-type nullifier that'd resolve this for future reference/usage. I will look at your substitute routine to see if that'd resolve it, but it's still a point that I need to resolve here. Again, thanks for your helps... "Roger Govier" wrote: Hi Steve only just picked up on this thread. It sounds as though you may have leading or trailing spaces, or, if the data has been copied from the web, then the non-breaking space Char(160). You could build in to the formula, substituting these characters with Null, but I would be more inclined to "clean" the source data. In a spar column on the source data sheet =SUBSTITUTE(SUSTITUTE(A10," ",""),Char(160),"") copy down as far as required Copy this "cleaned data" and paste Specialvalues back over the data in column A. Pete's formula does deal with case of the value not being matched (a good idea) but it could perhaps be achieved slightly more efficiently by using a single function call with Countif to check if E5 exists in column A. The following tests for Null value in E5 and No Match being found. =IF(E5="","",IF(COUNTIF(SUMMARY!$A$10:$A$60,E5), INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SUMMARY!$A$10:$ A$60,0))),"") -- Regards Roger Govier "SteveDB1" wrote in message ... yea, it looks like we were posting within minutes of each other, and the list delay for the newsgroup was causing the overlaps. I understood/stand that if there's no value, the cell would remain blank. Which was indeed part of what I'd wanted. I also understood that Match would return an #N/A error if it did not fint an exact match. Which was why I'd thought of using it, but "mis-ordered" my version in comparing my original to yours, and Roger's. At this point, I'm thinking that it's a data-type issue, and am wondering if there is a data-type nullifier that'd resolve the issues. I am aware of --, and &"". I have had occurrences that I've tried both of those and they didn't work for my purposes-- and yes, I'd be willing to consider that there was something else that I'd missed which invalidated their affect. Again, thank you for your time. Steve "Pete_UK" wrote: Steve, our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16 pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*(E5,SUMMARY!$A$10:$A$60 ,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Hi Steve
It might help if I could spell Substitute correctly, twice in succession<bg If you used =--SUBSTITUTE(SUBSTITUTE(A10," ",""),CHAR(160),"") That would also turn any text numbers into numeric. If the value being converted was true text, however, the -- would cause #VALUE to be shown. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi Roger, The data was not copied from the web. One of the things I did find was, as stated, a data-type difference between the two worksheets. I don't know if you remember-- enough time has passed-- but I'd posted back in October of 06 asking about troubles I was having with Sumproduct. Harley had tried answering my posts, and we both ended up getting short with each other. At that point you'd stepped in, and helped me work through the details of my problem. Which wound up being a data-type difference. I now use &"" in all my work with sumproduct because I've found it to work the best. This morning, after I'd reposted, I decided to look at my data-types and found that my data on the Sum page was text in some cases, and in others it was general. The data on my sheet where the equation was located was mostly general, with an occasional number data-type. As I found that, I decided to change the data-type by standard means, and then go through and activate each cell, thus "forcing" the conversion. I then checked my base sheet, and all of the data I wanted was there. Hence, I'm now wondering if there's a data-type nullifier that'd resolve this for future reference/usage. I will look at your substitute routine to see if that'd resolve it, but it's still a point that I need to resolve here. Again, thanks for your helps... "Roger Govier" wrote: Hi Steve only just picked up on this thread. It sounds as though you may have leading or trailing spaces, or, if the data has been copied from the web, then the non-breaking space Char(160). You could build in to the formula, substituting these characters with Null, but I would be more inclined to "clean" the source data. In a spar column on the source data sheet =SUBSTITUTE(SUSTITUTE(A10," ",""),Char(160),"") copy down as far as required Copy this "cleaned data" and paste Specialvalues back over the data in column A. Pete's formula does deal with case of the value not being matched (a good idea) but it could perhaps be achieved slightly more efficiently by using a single function call with Countif to check if E5 exists in column A. The following tests for Null value in E5 and No Match being found. =IF(E5="","",IF(COUNTIF(SUMMARY!$A$10:$A$60,E5), INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SUMMARY!$A$10:$ A$60,0))),"") -- Regards Roger Govier "SteveDB1" wrote in message ... yea, it looks like we were posting within minutes of each other, and the list delay for the newsgroup was causing the overlaps. I understood/stand that if there's no value, the cell would remain blank. Which was indeed part of what I'd wanted. I also understood that Match would return an #N/A error if it did not fint an exact match. Which was why I'd thought of using it, but "mis-ordered" my version in comparing my original to yours, and Roger's. At this point, I'm thinking that it's a data-type issue, and am wondering if there is a data-type nullifier that'd resolve the issues. I am aware of --, and &"". I have had occurrences that I've tried both of those and they didn't work for my purposes-- and yes, I'd be willing to consider that there was something else that I'd missed which invalidated their affect. Again, thank you for your time. Steve "Pete_UK" wrote: Steve, our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16 pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*(E5,SUMMARY!$A$10:$A$60 ,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
Hi Steve,
(I'm back !!) a slightly faster approach than Roger's formula is to use Find & Replace to get rid of your spaces. Highlight column A of the SUMMARY sheet by clicking on the column identifier. Then do CTRL-H as follows: Find what: type a single space Replace with: leave blank Click Replace All Hope this helps. Pete On Feb 3, 8:03*pm, SteveDB1 wrote: Hi Roger, The data was not copied from the web. One of the things I did find was, as stated, a data-type difference between the two worksheets. I don't know if you remember-- enough time has passed-- but I'd posted back in October of 06 asking about troubles I was having with Sumproduct. Harley had tried answering my posts, and we both ended up getting short with each other. At that point you'd stepped in, and helped me work through the details of my problem. Which wound up being a data-type difference. I now use &"" in all my work with sumproduct because I've found it to work the best. This morning, after I'd reposted, I decided to look at my data-types and found that my data on the Sum page was text in some cases, and in others it was general. The data on my sheet where the equation was located was mostly general, with an occasional number data-type. As I found that, I decided to change the data-type by standard means, and then go through and activate each cell, thus "forcing" the conversion. I then checked my base sheet, and all of the data I wanted was there. Hence, I'm now wondering if there's a data-type nullifier that'd resolve this for future reference/usage. I will look at your substitute routine to see if that'd resolve it, but it's still a point that I need to resolve here. Again, thanks for your helps... "Roger Govier" wrote: Hi Steve only just picked up on this thread. It sounds as though you may have leading or trailing spaces, or, if the data has been copied from the web, then the non-breaking space Char(160). You could build in to the formula, substituting these characters with Null, but I would be more inclined to "clean" the source data. In a spar column on the source data sheet =SUBSTITUTE(SUSTITUTE(A10," ",""),Char(160),"") copy down as far as required Copy this "cleaned data" and paste Specialvalues back over the data in column A. Pete's formula does deal with case of the value not being matched (a good idea) but it could perhaps be achieved slightly more efficiently by using a single function call with Countif to check if E5 exists in column A. The following tests for Null value in E5 and No Match being found. =IF(E5="","",IF(COUNTIF(SUMMARY!$A$10:$A$60,E5), INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SUMMARY!$A$10:$ A$60,0))),"") -- Regards Roger Govier "SteveDB1" wrote in message ... yea, it looks like we were posting within minutes of each other, and the list delay for the newsgroup was causing the overlaps. I understood/stand that if there's no value, the cell would remain blank. Which was indeed part of what I'd wanted. I also understood that Match would return an #N/A error if it did not fint an exact match. Which was why I'd thought of using it, but "mis-ordered" my version in comparing my original to yours, and Roger's. At this point, I'm thinking that it's a data-type issue, and am wondering if there is a data-type nullifier that'd resolve the issues. I am aware of --, and &"". I have had occurrences that I've tried both of those and they didn't work for my purposes-- and yes, I'd be willing to consider that there was something else that I'd missed which invalidated their affect. Again, thank you for your time. Steve "Pete_UK" wrote: Steve, our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16 pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCH**(E5,SUMMARY!$A$10:$A$60 ,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60**,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and *other elements are all accurate-- * double, and triple checking myself-- and I keep getting the identical response-- * #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e.., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Followup to "worksheet function"
So, based on both of your responses, you don't think that it's a data-type
matter? "Pete_UK" wrote: Hi Steve, (I'm back !!) a slightly faster approach than Roger's formula is to use Find & Replace to get rid of your spaces. Highlight column A of the SUMMARY sheet by clicking on the column identifier. Then do CTRL-H as follows: Find what: type a single space Replace with: leave blank Click Replace All Hope this helps. Pete On Feb 3, 8:03 pm, SteveDB1 wrote: Hi Roger, The data was not copied from the web. One of the things I did find was, as stated, a data-type difference between the two worksheets. I don't know if you remember-- enough time has passed-- but I'd posted back in October of 06 asking about troubles I was having with Sumproduct. Harley had tried answering my posts, and we both ended up getting short with each other. At that point you'd stepped in, and helped me work through the details of my problem. Which wound up being a data-type difference. I now use &"" in all my work with sumproduct because I've found it to work the best. This morning, after I'd reposted, I decided to look at my data-types and found that my data on the Sum page was text in some cases, and in others it was general. The data on my sheet where the equation was located was mostly general, with an occasional number data-type. As I found that, I decided to change the data-type by standard means, and then go through and activate each cell, thus "forcing" the conversion. I then checked my base sheet, and all of the data I wanted was there. Hence, I'm now wondering if there's a data-type nullifier that'd resolve this for future reference/usage. I will look at your substitute routine to see if that'd resolve it, but it's still a point that I need to resolve here. Again, thanks for your helps... "Roger Govier" wrote: Hi Steve only just picked up on this thread. It sounds as though you may have leading or trailing spaces, or, if the data has been copied from the web, then the non-breaking space Char(160). You could build in to the formula, substituting these characters with Null, but I would be more inclined to "clean" the source data. In a spar column on the source data sheet =SUBSTITUTE(SUSTITUTE(A10," ",""),Char(160),"") copy down as far as required Copy this "cleaned data" and paste Specialvalues back over the data in column A. Pete's formula does deal with case of the value not being matched (a good idea) but it could perhaps be achieved slightly more efficiently by using a single function call with Countif to check if E5 exists in column A. The following tests for Null value in E5 and No Match being found. =IF(E5="","",IF(COUNTIF(SUMMARY!$A$10:$A$60,E5), INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SUMMARY!$A$10:$ A$60,0))),"") -- Regards Roger Govier "SteveDB1" wrote in message ... yea, it looks like we were posting within minutes of each other, and the list delay for the newsgroup was causing the overlaps. I understood/stand that if there's no value, the cell would remain blank. Which was indeed part of what I'd wanted. I also understood that Match would return an #N/A error if it did not fint an exact match. Which was why I'd thought of using it, but "mis-ordered" my version in comparing my original to yours, and Roger's. At this point, I'm thinking that it's a data-type issue, and am wondering if there is a data-type nullifier that'd resolve the issues. I am aware of --, and &"". I have had occurrences that I've tried both of those and they didn't work for my purposes-- and yes, I'd be willing to consider that there was something else that I'd missed which invalidated their affect. Again, thank you for your time. Steve "Pete_UK" wrote: Steve, our posts are crossing. All 3 formulae are looking for exact matches, so the values have to match exactly. I suggest you have different data types in the lookup value and the lookup vector. I'm going out soon, so there won't be any more clashes in posting times. Pete On Feb 3, 4:16 pm, SteveDB1 wrote: Ok, now I'm really confused. I just tried Roger's version on two more files, and it works fine. Which makes me wonder if there are specific workbook attributes, or properties that would limit the working of the nested functions. "SteveDB1" wrote: Morning again folks. Yesterday I'd posted a question regarding looking up and comparing data between two worksheets, and placement of a marker in a tertiary column. Roger Govier, and Pete_UK graciously responded, and had provided an answer to my questions. They provided the following nested functions, with my modifications now included. The first is Roger's =IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0))) And these are Pete's =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*Â*(E5,SUMMARY!$A$10:$A$ 60,0))) =IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*Â*,2,0)) I've saved these in a notepad file so I have them at the ready for my use. At first, all three of these worked. All three provided the same exact answers when placed side by side. Later on in the day-- yesterday afternoon, and again this morning, I set each of them in a different worksheet, modified the column, and row values. Roger's now leaves me with the #N/A error, and Pete's leave the column blank with no corresponding letters. I've gone through and made sure that my row upper and lower limits are correct, that my columns, and other elements are all accurate-- double, and triple checking myself-- and I keep getting the identical response-- #N/A, or blanks (where letter values should be). My questions a 1- the most obvious.... what's wrong. 2- are there certain conditions beyond the obvious (columns, rows) that are required for this configuration of nested functions to work? I.e.., they all worked in one file, and now don't work in the remainder of my files (three that I've tried since the first yesterday morning). 3-I just went back, used Roger's original equation, selected the correct columns, placed absolute references B$9:..... to make sure that when I drag it down the top, and bottom limits aren't skewed. I'm still receiving the #N/A error. Your helps are appreciated. Best.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel "Worksheet Name" Building Function for Summary Sheet | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |