If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
T.Valko function
Morning.
Back in January I'd posted looking for some help on a function, and T.Valko helped, solving my issue. In using the function, I've run across something I was not expecting, and need to now solve that. The original post was: http://www.microsoft.com/communities...d-6f72a2f875a6 The problem that I've now recognized is that if there is a blank cell in my data on my source sheet, the first equation from the excel sheet provided will result in there being a false positive. I.e., it will show the count for all cells that are blank. In my initial function I'm checking to see how many names are missing, if any. As there'd normally have SOME value, regardless, I need to ignore blanks, and the placement of the blanks is not predictable, or standardized. |
#2
|
|||
|
|||
T.Valko function
Try these...
For the count of misssing names: =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1) To list those missing names (array entered): =IF(ROWS(D$22)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),IF(SubRng1"",ROW(SubRng1)) ),ROWS(D$22)))) -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning. Back in January I'd posted looking for some help on a function, and T.Valko helped, solving my issue. In using the function, I've run across something I was not expecting, and need to now solve that. The original post was: http://www.microsoft.com/communities...d-6f72a2f875a6 The problem that I've now recognized is that if there is a blank cell in my data on my source sheet, the first equation from the excel sheet provided will result in there being a false positive. I.e., it will show the count for all cells that are blank. In my initial function I'm checking to see how many names are missing, if any. As there'd normally have SOME value, regardless, I need to ignore blanks, and the placement of the blanks is not predictable, or standardized. |
#3
|
|||
|
|||
T.Valko function
Hi Biff,
Thank you for your reply, and willingness to pick this up again. I had thought it was working great until this anomaly.... Turns out now we have more than a handful of these files with blanks in my data set, so I want to rectify it. Ok, tried the addition of the countblank in the first equation. I get a twofold response-- on one data set it works great (names), but with the other (numeric values), it still shows blank cells-- a numeric value. In this specific case-- 41. I did a run through with the equation evaluator, and think that I'm following the logic ok. Think being the keyword. Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's. Sumproduct then returns 347-- the total count of the true responses from ISNA. Countblank then shows 306, leaving 41. However, all of my other tools show that all my data is accounted for. (I have a primary set of sumproduct eq'n's that do a numeric tally of the dataset's numeric side. They have two criteria to check, and then sum a third. Something Harlan Grove helped with back in '06.) In fact, in the data set that is working fine (names), the values with the first eq'n goes to zero. While I'm still trying to run through the equation evaluator with the second equation, I just realized something. The dataset that this new function does work with is all a text string-- names, etc.... The second dataset that the eq'n returns an erroneous response, is all 5 digit numbers. I'd faced this in the beginning with what Harlan Grove had explained on the sumproduct, and he provided a datatype nullifier to nullify the possible datatype variances/conflicts from one worksheet to another. As such, because the function is doing what I'd described above, I'm now wondering if this is my issue-- datatype conflicts? If so, how can I nullify the datatype with these functions? In my 2 criteria sumproducts, I used &"" (a single ampersand and two double quotes) to nulify this specific dataset. Would I do the same here? e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) If so, what location in the equation would they be placed? Again, thank you for your time. Best. "T. Valko" wrote: Try these... For the count of misssing names: =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1) To list those missing names (array entered): =IF(ROWS(D$22)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),IF(SubRng1"",ROW(SubRng1)) ),ROWS(D$22)))) -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning. Back in January I'd posted looking for some help on a function, and T.Valko helped, solving my issue. In using the function, I've run across something I was not expecting, and need to now solve that. The original post was: http://www.microsoft.com/communities...d-6f72a2f875a6 The problem that I've now recognized is that if there is a blank cell in my data on my source sheet, the first equation from the excel sheet provided will result in there being a false positive. I.e., it will show the count for all cells that are blank. In my initial function I'm checking to see how many names are missing, if any. As there'd normally have SOME value, regardless, I need to ignore blanks, and the placement of the blanks is not predictable, or standardized. . |
#4
|
|||
|
|||
T.Valko function
It sounds like these 5 digits numbers might actually be TEXT strings.
Numeric data like serial numbers, invoice numbers, etc. that start with leading zeros almost always cause problems if you're not careful. e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) In that formula, what you're doing is concatenating an empty TEXT string to the end of the value: cell_ref&"" So, if cell_ref = numeric 10, then cell_ref&"" = TEXT string 10. Numeric numbers and text numbers aren't equal when evaluated in most functions. Numeric 10 text 10. Notable exceptions are COUNTIF and SUMIF. They treat numeric numbers and text numbers as being equal. Numeric 10 = text 10. Also, if cell_ref is an empty cell then cell_ref&"" might cause problems if you forget to account for it. A...C 1....1 1 1 1 =SUMPRODUCT(--(A1:A5&""=C1&""))-COUNTBLANK(A1:A5) =3 As you can see that is obviously not the result you expect. See if this info helps. If not, then I might need to actually see the data for myself to see what's causing the problem. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Biff, Thank you for your reply, and willingness to pick this up again. I had thought it was working great until this anomaly.... Turns out now we have more than a handful of these files with blanks in my data set, so I want to rectify it. Ok, tried the addition of the countblank in the first equation. I get a twofold response-- on one data set it works great (names), but with the other (numeric values), it still shows blank cells-- a numeric value. In this specific case-- 41. I did a run through with the equation evaluator, and think that I'm following the logic ok. Think being the keyword. Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's. Sumproduct then returns 347-- the total count of the true responses from ISNA. Countblank then shows 306, leaving 41. However, all of my other tools show that all my data is accounted for. (I have a primary set of sumproduct eq'n's that do a numeric tally of the dataset's numeric side. They have two criteria to check, and then sum a third. Something Harlan Grove helped with back in '06.) In fact, in the data set that is working fine (names), the values with the first eq'n goes to zero. While I'm still trying to run through the equation evaluator with the second equation, I just realized something. The dataset that this new function does work with is all a text string-- names, etc.... The second dataset that the eq'n returns an erroneous response, is all 5 digit numbers. I'd faced this in the beginning with what Harlan Grove had explained on the sumproduct, and he provided a datatype nullifier to nullify the possible datatype variances/conflicts from one worksheet to another. As such, because the function is doing what I'd described above, I'm now wondering if this is my issue-- datatype conflicts? If so, how can I nullify the datatype with these functions? In my 2 criteria sumproducts, I used &"" (a single ampersand and two double quotes) to nulify this specific dataset. Would I do the same here? e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) If so, what location in the equation would they be placed? Again, thank you for your time. Best. "T. Valko" wrote: Try these... For the count of misssing names: =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1) To list those missing names (array entered): =IF(ROWS(D$22)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),IF(SubRng1"",ROW(SubRng1)) ),ROWS(D$22)))) -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning. Back in January I'd posted looking for some help on a function, and T.Valko helped, solving my issue. In using the function, I've run across something I was not expecting, and need to now solve that. The original post was: http://www.microsoft.com/communities...d-6f72a2f875a6 The problem that I've now recognized is that if there is a blank cell in my data on my source sheet, the first equation from the excel sheet provided will result in there being a false positive. I.e., it will show the count for all cells that are blank. In my initial function I'm checking to see how many names are missing, if any. As there'd normally have SOME value, regardless, I need to ignore blanks, and the placement of the blanks is not predictable, or standardized. . |
#5
|
|||
|
|||
T.Valko function
Morning again Biff.
Hope your weekend was a good one. Mine was a 3 day-er due to a furlough policy put in place to save money and keep everyone working... so far it's helped, and the money lost is offset by enjoying extra time off. Ok, back to it... I did try the &"" addition, and found a couple of things. 1- if I placed too many of them, it simply killed the Match, and ISNA elements of the equation. The ISNA got killed because no true responses were returned. And the Match only partially worked, and became a complex variable at that point. As the point of match is to either find an exact match, or fail, and when fail is returned, it provides the N/A# response, it appears to have been nullified, because, as you said-- the numeric 10 != the text 10. 2- I wound up getting a -306 (the answer of the countblank). I then kept reducing the &"" applications until I came to a single set for each. =SUMPRODUCT(--(ISNA(MATCH(APN!$J$6:$J$352&"",A$15:A$104,0))))-COUNTBLANK(APN!$J$6:$J$352) =IF(ROWS(L$5:L5)L$3,"All Permits Acct'd For",INDEX(APN!$J$6:$J$352,SMALL(IF(ISNA(MATCH(APN !$J$6:$J$352&"",A$15:A$104,0)),IF(APN!$J$6:$J$352 "",ROW(APN!$J$6:$J$352))),ROWS(L$5:L5)))) For the present, this appears to work. However, I have only tested it on one workbook with a single data set. Although, this particular dataset is one of the most complicated datasets we possess, so it seems to be a good test set. Your recognition of the 5 digit #'s being or acting like a text string is what precipitated the use of the &"" in the first place. We'd found numerous files and data sets in the beginning were responding oddly when we used the sumproduct as referenced last week, and this (&"") was Harlan's fix for it. The sumproduct has worked famously ever since, without fail- in my specific use. We found that the &"" appeared to act as a datatype nullifier to kill any datatype property that would prevent the sumproduct from recognizing two identical numbers of a differing datatype. As I read your previous post, and worked through the equation evaluator, it dawned on me the datatype variance might be my issue. Oh, and we never use leading zeroes. While there are datasets with them, we've worked out the code for stripping them when recognizing the data. Specifically because of the point you raised regardng them. If I find that any issues arise from the use of the &"" in this equation system you provided, I'll be back, and we can go at it again-- this time providing you a data set. again, thank you for your helps, and time. Best. SteveB. "T. Valko" wrote: It sounds like these 5 digits numbers might actually be TEXT strings. Numeric data like serial numbers, invoice numbers, etc. that start with leading zeros almost always cause problems if you're not careful. e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) In that formula, what you're doing is concatenating an empty TEXT string to the end of the value: cell_ref&"" So, if cell_ref = numeric 10, then cell_ref&"" = TEXT string 10. Numeric numbers and text numbers aren't equal when evaluated in most functions. Numeric 10 text 10. Notable exceptions are COUNTIF and SUMIF. They treat numeric numbers and text numbers as being equal. Numeric 10 = text 10. Also, if cell_ref is an empty cell then cell_ref&"" might cause problems if you forget to account for it. A...C 1....1 1 1 1 =SUMPRODUCT(--(A1:A5&""=C1&""))-COUNTBLANK(A1:A5) =3 As you can see that is obviously not the result you expect. See if this info helps. If not, then I might need to actually see the data for myself to see what's causing the problem. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Biff, Thank you for your reply, and willingness to pick this up again. I had thought it was working great until this anomaly.... Turns out now we have more than a handful of these files with blanks in my data set, so I want to rectify it. Ok, tried the addition of the countblank in the first equation. I get a twofold response-- on one data set it works great (names), but with the other (numeric values), it still shows blank cells-- a numeric value. In this specific case-- 41. I did a run through with the equation evaluator, and think that I'm following the logic ok. Think being the keyword. Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's. Sumproduct then returns 347-- the total count of the true responses from ISNA. Countblank then shows 306, leaving 41. However, all of my other tools show that all my data is accounted for. (I have a primary set of sumproduct eq'n's that do a numeric tally of the dataset's numeric side. They have two criteria to check, and then sum a third. Something Harlan Grove helped with back in '06.) In fact, in the data set that is working fine (names), the values with the first eq'n goes to zero. While I'm still trying to run through the equation evaluator with the second equation, I just realized something. The dataset that this new function does work with is all a text string-- names, etc.... The second dataset that the eq'n returns an erroneous response, is all 5 digit numbers. I'd faced this in the beginning with what Harlan Grove had explained on the sumproduct, and he provided a datatype nullifier to nullify the possible datatype variances/conflicts from one worksheet to another. As such, because the function is doing what I'd described above, I'm now wondering if this is my issue-- datatype conflicts? If so, how can I nullify the datatype with these functions? In my 2 criteria sumproducts, I used &"" (a single ampersand and two double quotes) to nulify this specific dataset. Would I do the same here? e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) If so, what location in the equation would they be placed? Again, thank you for your time. Best. "T. Valko" wrote: Try these... For the count of misssing names: =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1) To list those missing names (array entered): =IF(ROWS(D$22)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),IF(SubRng1"",ROW(SubRng1)) ),ROWS(D$22)))) -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning. Back in January I'd posted looking for some help on a function, and T.Valko helped, solving my issue. In using the function, I've run across something I was not expecting, and need to now solve that. The original post was: http://www.microsoft.com/communities...d-6f72a2f875a6 The problem that I've now recognized is that if there is a blank cell in my data on my source sheet, the first equation from the excel sheet provided will result in there being a false positive. I.e., it will show the count for all cells that are blank. In my initial function I'm checking to see how many names are missing, if any. As there'd normally have SOME value, regardless, I need to ignore blanks, and the placement of the blanks is not predictable, or standardized. . . |
#6
|
|||
|
|||
T.Valko function
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning again Biff. Hope your weekend was a good one. Mine was a 3 day-er due to a furlough policy put in place to save money and keep everyone working... so far it's helped, and the money lost is offset by enjoying extra time off. Ok, back to it... I did try the &"" addition, and found a couple of things. 1- if I placed too many of them, it simply killed the Match, and ISNA elements of the equation. The ISNA got killed because no true responses were returned. And the Match only partially worked, and became a complex variable at that point. As the point of match is to either find an exact match, or fail, and when fail is returned, it provides the N/A# response, it appears to have been nullified, because, as you said-- the numeric 10 != the text 10. 2- I wound up getting a -306 (the answer of the countblank). I then kept reducing the &"" applications until I came to a single set for each. =SUMPRODUCT(--(ISNA(MATCH(APN!$J$6:$J$352&"",A$15:A$104,0))))-COUNTBLANK(APN!$J$6:$J$352) =IF(ROWS(L$5:L5)L$3,"All Permits Acct'd For",INDEX(APN!$J$6:$J$352,SMALL(IF(ISNA(MATCH(APN !$J$6:$J$352&"",A$15:A$104,0)),IF(APN!$J$6:$J$352 "",ROW(APN!$J$6:$J$352))),ROWS(L$5:L5)))) For the present, this appears to work. However, I have only tested it on one workbook with a single data set. Although, this particular dataset is one of the most complicated datasets we possess, so it seems to be a good test set. Your recognition of the 5 digit #'s being or acting like a text string is what precipitated the use of the &"" in the first place. We'd found numerous files and data sets in the beginning were responding oddly when we used the sumproduct as referenced last week, and this (&"") was Harlan's fix for it. The sumproduct has worked famously ever since, without fail- in my specific use. We found that the &"" appeared to act as a datatype nullifier to kill any datatype property that would prevent the sumproduct from recognizing two identical numbers of a differing datatype. As I read your previous post, and worked through the equation evaluator, it dawned on me the datatype variance might be my issue. Oh, and we never use leading zeroes. While there are datasets with them, we've worked out the code for stripping them when recognizing the data. Specifically because of the point you raised regardng them. If I find that any issues arise from the use of the &"" in this equation system you provided, I'll be back, and we can go at it again-- this time providing you a data set. again, thank you for your helps, and time. Best. SteveB. "T. Valko" wrote: It sounds like these 5 digits numbers might actually be TEXT strings. Numeric data like serial numbers, invoice numbers, etc. that start with leading zeros almost always cause problems if you're not careful. e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) In that formula, what you're doing is concatenating an empty TEXT string to the end of the value: cell_ref&"" So, if cell_ref = numeric 10, then cell_ref&"" = TEXT string 10. Numeric numbers and text numbers aren't equal when evaluated in most functions. Numeric 10 text 10. Notable exceptions are COUNTIF and SUMIF. They treat numeric numbers and text numbers as being equal. Numeric 10 = text 10. Also, if cell_ref is an empty cell then cell_ref&"" might cause problems if you forget to account for it. A...C 1....1 1 1 1 =SUMPRODUCT(--(A1:A5&""=C1&""))-COUNTBLANK(A1:A5) =3 As you can see that is obviously not the result you expect. See if this info helps. If not, then I might need to actually see the data for myself to see what's causing the problem. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Biff, Thank you for your reply, and willingness to pick this up again. I had thought it was working great until this anomaly.... Turns out now we have more than a handful of these files with blanks in my data set, so I want to rectify it. Ok, tried the addition of the countblank in the first equation. I get a twofold response-- on one data set it works great (names), but with the other (numeric values), it still shows blank cells-- a numeric value. In this specific case-- 41. I did a run through with the equation evaluator, and think that I'm following the logic ok. Think being the keyword. Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's. Sumproduct then returns 347-- the total count of the true responses from ISNA. Countblank then shows 306, leaving 41. However, all of my other tools show that all my data is accounted for. (I have a primary set of sumproduct eq'n's that do a numeric tally of the dataset's numeric side. They have two criteria to check, and then sum a third. Something Harlan Grove helped with back in '06.) In fact, in the data set that is working fine (names), the values with the first eq'n goes to zero. While I'm still trying to run through the equation evaluator with the second equation, I just realized something. The dataset that this new function does work with is all a text string-- names, etc.... The second dataset that the eq'n returns an erroneous response, is all 5 digit numbers. I'd faced this in the beginning with what Harlan Grove had explained on the sumproduct, and he provided a datatype nullifier to nullify the possible datatype variances/conflicts from one worksheet to another. As such, because the function is doing what I'd described above, I'm now wondering if this is my issue-- datatype conflicts? If so, how can I nullify the datatype with these functions? In my 2 criteria sumproducts, I used &"" (a single ampersand and two double quotes) to nulify this specific dataset. Would I do the same here? e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB= RngB_1)*(SumRng)) If so, what location in the equation would they be placed? Again, thank you for your time. Best. "T. Valko" wrote: Try these... For the count of misssing names: =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1) To list those missing names (array entered): =IF(ROWS(D$22)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),IF(SubRng1"",ROW(SubRng1)) ),ROWS(D$22)))) -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning. Back in January I'd posted looking for some help on a function, and T.Valko helped, solving my issue. In using the function, I've run across something I was not expecting, and need to now solve that. The original post was: http://www.microsoft.com/communities...d-6f72a2f875a6 The problem that I've now recognized is that if there is a blank cell in my data on my source sheet, the first equation from the excel sheet provided will result in there being a false positive. I.e., it will show the count for all cells that are blank. In my initial function I'm checking to see how many names are missing, if any. As there'd normally have SOME value, regardless, I need to ignore blanks, and the placement of the blanks is not predictable, or standardized. . . |
Thread Tools | |
Display Modes | |
|
|