Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtotal, match?
Morning all.
I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name element to match my source data. I hope that's clear-- if not, please let me know. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Ok, I realized that I had this backwards, so I tried
Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
I hope that's clear-- if not, please let me know.
Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendum 2
I've now tried vlookup and it too isn't giving me what I'm looking for.
My eq arrangement is: =VLOOKUP(SUBTOTAL(109,range),range,1,FALSE) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Don't feel bad Biff.
I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Ok, need some of the details...
Sheet1 A2:A10 = parcel numbers Sheet2 A2:A10 = parcel numbers You filter both sheets on parcel number 999. Now what? You said you want to compare owners. Ok, so where do we look to see if the owners are the same? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't feel bad Biff. I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Details:
Sht1!A4:A188 = parcel #'s Sht2!A4:A289 = parcel #'s In column F (both sheets) the name of the owner appears. I.e., the row for the specifed parcel will show all pertinent data, of which, column F shows owner's name. Below all my data, in an open area, I want to place the equation to check the filtered names. This will prevent me from having to check back and forth between both worksheets when the owner's name already matches. Something of an IF eq'n. I just want to have it say- 'ok' if values match, and the correct owner's name if they don't match. Hope that's clear.... Thanks again for all your help-- it's really appreciated. "T. Valko" wrote: Ok, need some of the details... Sheet1 A2:A10 = parcel numbers Sheet2 A2:A10 = parcel numbers You filter both sheets on parcel number 999. Now what? You said you want to compare owners. Ok, so where do we look to see if the owners are the same? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't feel bad Biff. I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Ok, let's start with this and go from there...
One thing to note, working with filtered data is not easy! Microsoft only provides for the most basic of analysis functions for filtered data. Sum, Average, Count, etc. This array formula** will return the *first* name from the range Sht1!F4:F188 when you filter (or don't filter) on a parcel number in column A. I assume you want the formula on Sht1. =INDEX(F4:F188,MATCH(1,SUBTOTAL(3,OFFSET(F4:F188,, ,ROW(F4:F188)-ROW(F4)+1)),0)) Now, if you want to compare that name to the name on Sht2 that is also filtered on the parcel number then we have to extract that name also: Array entered** : =INDEX(Sht2!F4:F289,MATCH(1,SUBTOTAL(3,OFFSET(Sht2 !F4:F289,,,ROW(Sht2!F4:F289)-ROW(Sht2!F4)+1)),0)) Then, you can compare the results of those 2 formulas: =IF(A1=B1,do_this,do_that) ** 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. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Details: Sht1!A4:A188 = parcel #'s Sht2!A4:A289 = parcel #'s In column F (both sheets) the name of the owner appears. I.e., the row for the specifed parcel will show all pertinent data, of which, column F shows owner's name. Below all my data, in an open area, I want to place the equation to check the filtered names. This will prevent me from having to check back and forth between both worksheets when the owner's name already matches. Something of an IF eq'n. I just want to have it say- 'ok' if values match, and the correct owner's name if they don't match. Hope that's clear.... Thanks again for all your help-- it's really appreciated. "T. Valko" wrote: Ok, need some of the details... Sheet1 A2:A10 = parcel numbers Sheet2 A2:A10 = parcel numbers You filter both sheets on parcel number 999. Now what? You said you want to compare owners. Ok, so where do we look to see if the owners are the same? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't feel bad Biff. I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Good morning Biff....
One way to tell I'm in trouble is when guys give me these kinds of notes...... "One thing to note, working with filtered data is not easy! Microsoft only provides for the most basic of analysis functions for filtered data. Sum, Average, Count, etc." Why is it that I'm always the one out on the cutting edge? ;-) Even my colleagues keep telling me that I'm just expecting too much from Excel! Who-da thunk it?!?!!! I'll try these and go from there. Thank you for your help-- it's IMMENSELY Appreciated!!! "T. Valko" wrote: Ok, let's start with this and go from there... One thing to note, working with filtered data is not easy! Microsoft only provides for the most basic of analysis functions for filtered data. Sum, Average, Count, etc. This array formula** will return the *first* name from the range Sht1!F4:F188 when you filter (or don't filter) on a parcel number in column A. I assume you want the formula on Sht1. =INDEX(F4:F188,MATCH(1,SUBTOTAL(3,OFFSET(F4:F188,, ,ROW(F4:F188)-ROW(F4)+1)),0)) Now, if you want to compare that name to the name on Sht2 that is also filtered on the parcel number then we have to extract that name also: Array entered** : =INDEX(Sht2!F4:F289,MATCH(1,SUBTOTAL(3,OFFSET(Sht2 !F4:F289,,,ROW(Sht2!F4:F289)-ROW(Sht2!F4)+1)),0)) Then, you can compare the results of those 2 formulas: =IF(A1=B1,do_this,do_that) ** 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. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Details: Sht1!A4:A188 = parcel #'s Sht2!A4:A289 = parcel #'s In column F (both sheets) the name of the owner appears. I.e., the row for the specifed parcel will show all pertinent data, of which, column F shows owner's name. Below all my data, in an open area, I want to place the equation to check the filtered names. This will prevent me from having to check back and forth between both worksheets when the owner's name already matches. Something of an IF eq'n. I just want to have it say- 'ok' if values match, and the correct owner's name if they don't match. Hope that's clear.... Thanks again for all your help-- it's really appreciated. "T. Valko" wrote: Ok, need some of the details... Sheet1 A2:A10 = parcel numbers Sheet2 A2:A10 = parcel numbers You filter both sheets on parcel number 999. Now what? You said you want to compare owners. Ok, so where do we look to see if the owners are the same? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't feel bad Biff. I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . . . |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
Hi again.
This looks great! Thank you. You da Man!!! (and my point on my previous post was that-- Yes, I am aware of the limits that Excel has placed on filtered data. This is why I come post and ask dense, thick-headed questions that are poorly phrased.) I know.... I am learning though. Again-- thank you for your help. "T. Valko" wrote: Ok, let's start with this and go from there... One thing to note, working with filtered data is not easy! Microsoft only provides for the most basic of analysis functions for filtered data. Sum, Average, Count, etc. This array formula** will return the *first* name from the range Sht1!F4:F188 when you filter (or don't filter) on a parcel number in column A. I assume you want the formula on Sht1. =INDEX(F4:F188,MATCH(1,SUBTOTAL(3,OFFSET(F4:F188,, ,ROW(F4:F188)-ROW(F4)+1)),0)) Now, if you want to compare that name to the name on Sht2 that is also filtered on the parcel number then we have to extract that name also: Array entered** : =INDEX(Sht2!F4:F289,MATCH(1,SUBTOTAL(3,OFFSET(Sht2 !F4:F289,,,ROW(Sht2!F4:F289)-ROW(Sht2!F4)+1)),0)) Then, you can compare the results of those 2 formulas: =IF(A1=B1,do_this,do_that) ** 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. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Details: Sht1!A4:A188 = parcel #'s Sht2!A4:A289 = parcel #'s In column F (both sheets) the name of the owner appears. I.e., the row for the specifed parcel will show all pertinent data, of which, column F shows owner's name. Below all my data, in an open area, I want to place the equation to check the filtered names. This will prevent me from having to check back and forth between both worksheets when the owner's name already matches. Something of an IF eq'n. I just want to have it say- 'ok' if values match, and the correct owner's name if they don't match. Hope that's clear.... Thanks again for all your help-- it's really appreciated. "T. Valko" wrote: Ok, need some of the details... Sheet1 A2:A10 = parcel numbers Sheet2 A2:A10 = parcel numbers You filter both sheets on parcel number 999. Now what? You said you want to compare owners. Ok, so where do we look to see if the owners are the same? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't feel bad Biff. I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . . . |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addendem
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi again. This looks great! Thank you. You da Man!!! (and my point on my previous post was that-- Yes, I am aware of the limits that Excel has placed on filtered data. This is why I come post and ask dense, thick-headed questions that are poorly phrased.) I know.... I am learning though. Again-- thank you for your help. "T. Valko" wrote: Ok, let's start with this and go from there... One thing to note, working with filtered data is not easy! Microsoft only provides for the most basic of analysis functions for filtered data. Sum, Average, Count, etc. This array formula** will return the *first* name from the range Sht1!F4:F188 when you filter (or don't filter) on a parcel number in column A. I assume you want the formula on Sht1. =INDEX(F4:F188,MATCH(1,SUBTOTAL(3,OFFSET(F4:F188,, ,ROW(F4:F188)-ROW(F4)+1)),0)) Now, if you want to compare that name to the name on Sht2 that is also filtered on the parcel number then we have to extract that name also: Array entered** : =INDEX(Sht2!F4:F289,MATCH(1,SUBTOTAL(3,OFFSET(Sht2 !F4:F289,,,ROW(Sht2!F4:F289)-ROW(Sht2!F4)+1)),0)) Then, you can compare the results of those 2 formulas: =IF(A1=B1,do_this,do_that) ** 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. -- Biff Microsoft Excel MVP "Steve" wrote in message ... Details: Sht1!A4:A188 = parcel #'s Sht2!A4:A289 = parcel #'s In column F (both sheets) the name of the owner appears. I.e., the row for the specifed parcel will show all pertinent data, of which, column F shows owner's name. Below all my data, in an open area, I want to place the equation to check the filtered names. This will prevent me from having to check back and forth between both worksheets when the owner's name already matches. Something of an IF eq'n. I just want to have it say- 'ok' if values match, and the correct owner's name if they don't match. Hope that's clear.... Thanks again for all your help-- it's really appreciated. "T. Valko" wrote: Ok, need some of the details... Sheet1 A2:A10 = parcel numbers Sheet2 A2:A10 = parcel numbers You filter both sheets on parcel number 999. Now what? You said you want to compare owners. Ok, so where do we look to see if the owners are the same? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Don't feel bad Biff. I get pretty dense too..... Hence my poor explanation...... ;-( Ok, what I'm doing is filtering two wksheets, based on a single criteria. In my case, a property parcel #. My goal is to check the name of the owner's on each wksheet. One wksheet I know is valid, the other is to get it correctly updated. If the name from pageA matches the name on pageB, then great-- some kind of a TRUE statement, as in an IF equation. I've tried vlookup, match, subtotal, and an IF. So far my attempts have met with #N/A Errors. It seems to me that vlookup should work, if I have the correct order. Because I'm filtering, it seems that subtotal would be required too. At this point, I'm wondering if some UDF might be required, but I have no idea where to take that idea. "T. Valko" wrote: I hope that's clear-- if not, please let me know. Well, it's not real clear to me. But sometimes I'm really dense! It sounds/looks like you want to a do a "SUM IF" on a filtered range? "SUM column B IF column A equals Joe" on a filtered list. Am I close? -- Biff Microsoft Excel MVP "Steve" wrote in message ... Ok, I realized that I had this backwards, so I tried Match(subtotal(109,range),ArrayRng,0) and it doesn't work either. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort macro, subtotal and add lines after subtotal | Excel Discussion (Misc queries) | |||
Double Pivot Table - Match Subtotal Rows | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |