Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
Hello,
I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#2
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
What is the definition of a set here?
-- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#3
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
It is an excel file and the data are in separate rectangular ranges.
Herbert "Bob Phillips" ¦b¶l¥ó ¤¤¼¶¼g... What is the definition of a set here? -- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#4
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
Herbert,
I think you'd be better served asking this question in one of the Excel newsgroups. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia --------------------------- "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#5
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
Excel ranges can be treated like tables in Excel. So you can probably use
ADO to do the same thing. http://support.microsoft.com/default...b;en-us;278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... It is an excel file and the data are in separate rectangular ranges. Herbert "Bob Phillips" ¦b¶l¥ó ¤¤¼¶¼g... What is the definition of a set here? -- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#6
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
Hello,
But my data are arranged like this: x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 y1 y2 y3 y4 y5 y6 y7 y8 y9 y10 y11 y12 y13 y14 y15 So it's like I want to extract the y's that are not in the x's. It's good to know that ADO can be used directly within Excel, but it seems that it cannot be used in my case, right? At least not very directly. So I'm wondering if there're any functions out there that can do this extraction. Thanks. Herbert "Tom Ogilvy" ¦b¶l¥ó ¤¤¼¶¼g... Excel ranges can be treated like tables in Excel. So you can probably use ADO to do the same thing. http://support.microsoft.com/default...b;en-us;278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... It is an excel file and the data are in separate rectangular ranges. Herbert "Bob Phillips" ¦b¶l¥ó ¤¤¼¶¼g... What is the definition of a set here? -- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#7
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
depending on how large you range is, I would just go off to the right and
use a formula like (assume the x's start in A1) =if(countif(RangeofY's,A1)=0,A1,"") then drag fill that down and across in the same pattern as you X values this will give you your X values not included in Y. This could be accplished in a similar manner using a macro. -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... Hello, But my data are arranged like this: x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 y1 y2 y3 y4 y5 y6 y7 y8 y9 y10 y11 y12 y13 y14 y15 So it's like I want to extract the y's that are not in the x's. It's good to know that ADO can be used directly within Excel, but it seems that it cannot be used in my case, right? At least not very directly. So I'm wondering if there're any functions out there that can do this extraction. Thanks. Herbert "Tom Ogilvy" ¦b¶l¥ó ¤¤¼¶¼g... Excel ranges can be treated like tables in Excel. So you can probably use ADO to do the same thing. http://support.microsoft.com/default...b;en-us;278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... It is an excel file and the data are in separate rectangular ranges. Herbert "Bob Phillips" ¦b¶l¥ó ¤¤¼¶¼g... What is the definition of a set here? -- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
#8
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
I still don't understand what you want to do. Is it
1) Identify rows in the y-block for which there is no matching row in the x-block? If so, treat each block as a table and use ADO or DAO. 2) Identify cells in the y-block that have a different value from the corresponding cell in the x-block (e.g. if the value of y5 is different from the value of x5)? If so, use ordinary Excel formulas in a third block) 3) Something else? If so, please explain unambiguously, preferably with the aid of sample data. On Fri, 25 Mar 2005 22:59:46 +0800, "Herbert Chan" wrote: Hello, But my data are arranged like this: x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 y1 y2 y3 y4 y5 y6 y7 y8 y9 y10 y11 y12 y13 y14 y15 So it's like I want to extract the y's that are not in the x's. It's good to know that ADO can be used directly within Excel, but it seems that it cannot be used in my case, right? At least not very directly. So I'm wondering if there're any functions out there that can do this extraction. Thanks. Herbert "Tom Ogilvy" ¦b¶l¥ó ¤¤¼¶¼g... Excel ranges can be treated like tables in Excel. So you can probably use ADO to do the same thing. http://support.microsoft.com/default...b;en-us;278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... It is an excel file and the data are in separate rectangular ranges. Herbert "Bob Phillips" ¦b¶l¥ó ¤¤¼¶¼g... What is the definition of a set here? -- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#9
Posted to microsoft.public.access.modulesdaovba,microsoft.public.excel.programming
|
|||
|
|||
VBA Function that mimics the Access Not Matching Query
whoops, you said y's not in the x's and I gave you x's not in the y's. Make
the following adjustments. Formula would be adjacent to the y's and assume the first y is in A1. (assume the y's start in A1) =if(countif(RangeofX's,A1)=0,A1,"") -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... depending on how large you range is, I would just go off to the right and use a formula like (assume the x's start in A1) =if(countif(RangeofY's,A1)=0,A1,"") then drag fill that down and across in the same pattern as you X values this will give you your X values not included in Y. This could be accplished in a similar manner using a macro. -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... Hello, But my data are arranged like this: x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 y1 y2 y3 y4 y5 y6 y7 y8 y9 y10 y11 y12 y13 y14 y15 So it's like I want to extract the y's that are not in the x's. It's good to know that ADO can be used directly within Excel, but it seems that it cannot be used in my case, right? At least not very directly. So I'm wondering if there're any functions out there that can do this extraction. Thanks. Herbert "Tom Ogilvy" ¦b¶l¥ó ¤¤¼¶¼g... Excel ranges can be treated like tables in Excel. So you can probably use ADO to do the same thing. http://support.microsoft.com/default...b;en-us;278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks -- Regards, Tom Ogilvy "Herbert Chan" wrote in message ... It is an excel file and the data are in separate rectangular ranges. Herbert "Bob Phillips" ¦b¶l¥ó ¤¤¼¶¼g... What is the definition of a set here? -- HTH RP (remove nothere from the email address if mailing direct) "Herbert Chan" wrote in message ... Hello, I've a spreadsheet in Excel and I have some data. I want to be able to extract the data from set1 that are not in set2. In Access, that will be a nonmatching query. However, in Excel, how do I do it with VBA function? Any written functions already out there? Thanks. Herbert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to write a PRODUCT formula that mimics a SUMIF | Excel Worksheet Functions | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Spreadsheet function that mimics "LIKE" | Excel Programming |