|
Help
Howdee all.
I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. |
Help
Can you instert the column on your source sheet and use vlookup function (for
example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. |
Help
Hi Eva,
Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. |
Help
Try this...
In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. |
Help
Morning T.
Thank you for your response. Ok, I tried your formulas and I do indeed get the correct number of missing names. I then tried the second formula, and get the wrong name. I double checked to make sure, and did an IF test to verify. At this point I'm willing to buy that I'm doing something wrong. The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Also, I'm not entirely clear on your explanation of arrays. I get the key-stroke selection part, but not the HAVE TO part. I know-- consider me half blind... well, make that insatiably curious. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . |
Help
The changes that I made were instead of selecting
the entire column A, I only selected the range of interest. Ok, that's where you went wrong! We can reference only the specific range of interest but that'll require a somewhat longer, more complicated formula. Here's a small sample file that demonstrates this using the original formula and the modified formula referencing only the specific range. Steve.xls 18kb http://cjoint.com/?bfr6kgYdZD -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning T. Thank you for your response. Ok, I tried your formulas and I do indeed get the correct number of missing names. I then tried the second formula, and get the wrong name. I double checked to make sure, and did an IF test to verify. At this point I'm willing to buy that I'm doing something wrong. The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Also, I'm not entirely clear on your explanation of arrays. I get the key-stroke selection part, but not the HAVE TO part. I know-- consider me half blind... well, make that insatiably curious. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . |
Help
ok, apparently my initial response to you got lost in the network somewhere.
I've tried the formulas, and have both positive, and negative results. First, the first function provided the correct number of missing names. Second, the next function did not provide the correct name. I even double checked to make sure I wasn't reading something incorrectly-- with an IF equation. I did make the necessary modifications to the equations to match my specific worksheet configurations. I changed SubRng1 to the entire range-- I'm guessing you only selected 5 rows for sampling purposes. I then changed Rng1 from the entire column, to the specific range I needed. Then, the same for Rng2. The logic is fairly clear-- with the exception of your statement on arrays. I've been using Sumproduct for over 3 years now, and hadn't heard the keystroke arrangement you state before. For this reason, I'd appreciate some further clarification on that. Thus my two versions of your strings are as follows: =SUMPRODUCT(--(ISNA(MATCH(APN!F$5:F$100,C$12:C$43,0)))) Where the APN!F$5:F$100 is my source data array and C$12:C$43 is my summary data array. Then, =IF(ROWS(H$12:H12)G$12,"",INDEX(APN!F$5:F$100,SMA LL(IF(ISNA(MATCH(APN!F$5:F$100,C$12:C$43,0)),ROW(A PN!F$5:F$100)),ROWS(H$12:H12)))) Where h$12:h12, and G$12 are my equation placements/starting positions. APN!F$5:F$100, and C$12:C$43 are as above- Source, and summary arrays, respectively. I'm willing to buy that I missed something. I did however get the incorrect name. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . |
Help
Don't know if anyone can see this or not-- it appears the board isn't
accepting new posts..... anyone else having this issue? "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . |
Help
I posted this earlier. I guess you didn't see it?
The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Ok, that's where you went wrong! We can reference only the specific range of interest but that'll require a somewhat longer, more complicated formula. Here's a small sample file that demonstrates this using the original formula and the modified formula referencing only the specific range. Steve.xls 18kb http://cjoint.com/?bfr6kgYdZD -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't know if anyone can see this or not-- it appears the board isn't accepting new posts..... anyone else having this issue? "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . |
Help
No, we can't see this. :-)
-- David Biddulph "Steve" wrote in message ... Don't know if anyone can see this or not-- it appears the board isn't accepting new posts..... anyone else having this issue? .... |
Help
smart alec.....
;-) "David Biddulph" wrote: No, we can't see this. :-) -- David Biddulph "Steve" wrote in message ... Don't know if anyone can see this or not-- it appears the board isn't accepting new posts..... anyone else having this issue? .... . |
Help
Hi T.
For some reason all of today's posts were blocked for 4 or 5 hours here.... I could see everything from last night, but nothing from today- until about an hour ago. Ok, got your point-- Rng1 MUST full column. Also, I see that in setting the array functions-- with the {}, the cell has to be active. It's working now. Then, I'm guessing that the formula 2 is the one that doesn't require the full column array? It too works. One more question-- Can I set this up with a macro/user form? If so, what would I need to activate the array? Or would I just do it after the equation is set in place? Thank you. Best. Have a "T. Valko" wrote: The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Ok, that's where you went wrong! We can reference only the specific range of interest but that'll require a somewhat longer, more complicated formula. Here's a small sample file that demonstrates this using the original formula and the modified formula referencing only the specific range. Steve.xls 18kb http://cjoint.com/?bfr6kgYdZD -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning T. Thank you for your response. Ok, I tried your formulas and I do indeed get the correct number of missing names. I then tried the second formula, and get the wrong name. I double checked to make sure, and did an IF test to verify. At this point I'm willing to buy that I'm doing something wrong. The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Also, I'm not entirely clear on your explanation of arrays. I get the key-stroke selection part, but not the HAVE TO part. I know-- consider me half blind... well, make that insatiably curious. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . . |
Help
Can I set this up with a macro/user form?
I'm sure you could but I'm not sure how you'd do that. My "forte" is formulas! If you're wanting to load those values into a user form you may do it a completely different way that doesn't even use a formula (unless you want both, the extracted list on the worksheet and then use that list to dump into the user form). I would suggest you post this to the programming forum. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi T. For some reason all of today's posts were blocked for 4 or 5 hours here.... I could see everything from last night, but nothing from today- until about an hour ago. Ok, got your point-- Rng1 MUST full column. Also, I see that in setting the array functions-- with the {}, the cell has to be active. It's working now. Then, I'm guessing that the formula 2 is the one that doesn't require the full column array? It too works. One more question-- Can I set this up with a macro/user form? If so, what would I need to activate the array? Or would I just do it after the equation is set in place? Thank you. Best. Have a "T. Valko" wrote: The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Ok, that's where you went wrong! We can reference only the specific range of interest but that'll require a somewhat longer, more complicated formula. Here's a small sample file that demonstrates this using the original formula and the modified formula referencing only the specific range. Steve.xls 18kb http://cjoint.com/?bfr6kgYdZD -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning T. Thank you for your response. Ok, I tried your formulas and I do indeed get the correct number of missing names. I then tried the second formula, and get the wrong name. I double checked to make sure, and did an IF test to verify. At this point I'm willing to buy that I'm doing something wrong. The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Also, I'm not entirely clear on your explanation of arrays. I get the key-stroke selection part, but not the HAVE TO part. I know-- consider me half blind... well, make that insatiably curious. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . . |
Help
Fair enough.
Thanks again for your help. "T. Valko" wrote: Can I set this up with a macro/user form? I'm sure you could but I'm not sure how you'd do that. My "forte" is formulas! If you're wanting to load those values into a user form you may do it a completely different way that doesn't even use a formula (unless you want both, the extracted list on the worksheet and then use that list to dump into the user form). I would suggest you post this to the programming forum. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi T. For some reason all of today's posts were blocked for 4 or 5 hours here.... I could see everything from last night, but nothing from today- until about an hour ago. Ok, got your point-- Rng1 MUST full column. Also, I see that in setting the array functions-- with the {}, the cell has to be active. It's working now. Then, I'm guessing that the formula 2 is the one that doesn't require the full column array? It too works. One more question-- Can I set this up with a macro/user form? If so, what would I need to activate the array? Or would I just do it after the equation is set in place? Thank you. Best. Have a "T. Valko" wrote: The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Ok, that's where you went wrong! We can reference only the specific range of interest but that'll require a somewhat longer, more complicated formula. Here's a small sample file that demonstrates this using the original formula and the modified formula referencing only the specific range. Steve.xls 18kb http://cjoint.com/?bfr6kgYdZD -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning T. Thank you for your response. Ok, I tried your formulas and I do indeed get the correct number of missing names. I then tried the second formula, and get the wrong name. I double checked to make sure, and did an IF test to verify. At this point I'm willing to buy that I'm doing something wrong. The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Also, I'm not entirely clear on your explanation of arrays. I get the key-stroke selection part, but not the HAVE TO part. I know-- consider me half blind... well, make that insatiably curious. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . . . |
Help
You're welcome!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... Fair enough. Thanks again for your help. "T. Valko" wrote: Can I set this up with a macro/user form? I'm sure you could but I'm not sure how you'd do that. My "forte" is formulas! If you're wanting to load those values into a user form you may do it a completely different way that doesn't even use a formula (unless you want both, the extracted list on the worksheet and then use that list to dump into the user form). I would suggest you post this to the programming forum. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi T. For some reason all of today's posts were blocked for 4 or 5 hours here.... I could see everything from last night, but nothing from today- until about an hour ago. Ok, got your point-- Rng1 MUST full column. Also, I see that in setting the array functions-- with the {}, the cell has to be active. It's working now. Then, I'm guessing that the formula 2 is the one that doesn't require the full column array? It too works. One more question-- Can I set this up with a macro/user form? If so, what would I need to activate the array? Or would I just do it after the equation is set in place? Thank you. Best. Have a "T. Valko" wrote: The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Ok, that's where you went wrong! We can reference only the specific range of interest but that'll require a somewhat longer, more complicated formula. Here's a small sample file that demonstrates this using the original formula and the modified formula referencing only the specific range. Steve.xls 18kb http://cjoint.com/?bfr6kgYdZD -- Biff Microsoft Excel MVP "Steve" wrote in message ... Morning T. Thank you for your response. Ok, I tried your formulas and I do indeed get the correct number of missing names. I then tried the second formula, and get the wrong name. I double checked to make sure, and did an IF test to verify. At this point I'm willing to buy that I'm doing something wrong. The changes that I made were instead of selecting the entire column A, I only selected the range of interest. Also, I'm not entirely clear on your explanation of arrays. I get the key-stroke selection part, but not the HAVE TO part. I know-- consider me half blind... well, make that insatiably curious. "T. Valko" wrote: Try this... In the formulas: Rng1 refers to Source sheet A:A SubRng1 refers to Source sheet A$2:A$7 Rng2 refers to Summary sheet A$2:A$5 On the Summary sheet enter this formula in C2. This will return the count of missing names. =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0)))) On the Summary sheet enter this array formula** in D2: =IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) ) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy the formula in D2 down until you get blanks. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi Eva, Thank you for your response. With my initial check of this function, I see that it will leave the cell blank if the name exists on my source sheet. However, if the cell for my criteria is blank, or if the value is not on the source sheet, it returns a #N/A error. I then reversed the placement from my sum sheet to my source sheet, as well as placed a name in a cell that does not exist on my sum sheet. I made the necessary sheet name modifications, and reduced the A:A to the range/column I needed as well. Ok, I got a response, but it's reversed from my desired goal. I.e., I put the function on my source sheet- my desire/need is for it to be on the summary sheet. For the location that has the new name, it returned a #N/A error. While this would work-- it needs to be "dummy proof" in that someone who's never seen this kind of thing before needs to see a name, and not an error note. My experience with VLookup is real limited, so as I understand it, it's looking for a specific element, throughout an array, within a specified column. E.g. SourceSht SumSht John Sam Dave Dave Betty Joe Don John Joe Sam I need to show on the SumSht that I missed putting in Betty- in one cell, and Don, in another cell. I hope this helps make it clearer. Again-- thank you. "Eva" wrote: Can you instert the column on your source sheet and use vlookup function (for example if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false) assuming that your lookup data are in column A in source and lookup value are also in col A (change as you wish) and then filter all out to see which ones are new? (all non blank or all with error messages) -- Click yes if helped. Greatly appreciated Eva "Steve" wrote: Howdee all. I was just presented with a problem that has got me curious. While it's something I have thought about before, I never knew where to begin with it. I have two data sheets. Each have names, values, etc...-- one is a source sheet, and the second is a summary sheet of the source data. On the Summary sheet, I have a bunch of sumproduct functions with two criteria arrays, and one sum array. At times I'm short a name or two, and must manually work through my source sheet to identify a name that's missing on my Summary sheet (As you might imagine, it's a pain-staking process for longer data-sets). What I want to do is to set up a function that will look at all the names on the source sheet, and compare them to the summary sheet names. If a name is missing on my summary sheet, I want to have it tell me the name that's missing. I've used Match(), if(), and maybe one or two others (that I can't remember at the moment) to try this, and Match just tells me if the name doesn't exist-- #N/A error. Does anyone have an idea on either a single worksheet function, or a combination of nested worksheet functions to accomplish this? I don't care where the name is on my source sheet-- just if I've missed having it on my summary sheet-- and what the name actually is. Thank you for your helps-- in advance. Best. . . . |
| All times are GMT +1. The time now is 10:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com