Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
I am searching a talbe for dates that match a given month and ned to find the
row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
Hi!
I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
Hi Biff.
Yes, it is all related to the same problem. I have been messing around with it trying various ways to get what I want and got stuck once again. I am definitely interested in what you have figured out. Performance isn't a big issue because it is something that only has to be run once a month and I would rather have the computer sit and work for an hour then have to go through all these records and manually create the lists. Thank you so much for your help! "T. Valko" wrote: Hi! I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
Ok....
Would you like me to post a sample file or try to explain it all in writing? Biff "SteveT" wrote in message ... Hi Biff. Yes, it is all related to the same problem. I have been messing around with it trying various ways to get what I want and got stuck once again. I am definitely interested in what you have figured out. Performance isn't a big issue because it is something that only has to be run once a month and I would rather have the computer sit and work for an hour then have to go through all these records and manually create the lists. Thank you so much for your help! "T. Valko" wrote: Hi! I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
A sample file would be easiest, but it wouldn't help other people who may
find this thread after the file is removed. So either way! "T. Valko" wrote: Ok.... Would you like me to post a sample file or try to explain it all in writing? Biff "SteveT" wrote in message ... Hi Biff. Yes, it is all related to the same problem. I have been messing around with it trying various ways to get what I want and got stuck once again. I am definitely interested in what you have figured out. Performance isn't a big issue because it is something that only has to be run once a month and I would rather have the computer sit and work for an hour then have to go through all these records and manually create the lists. Thank you so much for your help! "T. Valko" wrote: Hi! I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
Ok, here goes.......
Here is the sample file: SteveT.xls 76kb http://cjoint.com/?czgYgNgNxj The file contains VBA code. 1 macro provided by Tom Ogilvy and a UDF provided by Chip Pearson. Beleive it or not the hardest thing about this was getting the addresses the way you wanted them. I had originally used some helper columns to create an array of the addresses and then used another formula to transpose those into a single column. This worked but was clunky! So I went over to the programming group and got some help by some of the good folks over there. Here's a link to that thread: http://tinyurl.com/2hajwo I've used both the macro and the UDF in this file. The macro was used on sheet Dest and the UDF was used on sheet UDF. In the sample file I've added a column to count how many dates in each address meet the month criteria entered in cell A2 of sheet Data. This column is used by both the macro and the UDF. The macro is faster but is not dynamic. The UDF is slower but is dynamic. You'll have to decide which method to use. I kind of like things being dynamic so my tendency would be towards the UDF. See the linked thread above for Chip's instructions on the UDF. The macro is module 1 and the UDF is module 2 The formulas I've used contain named ranges. On both the Dest sheet and the UDF sheet the formulas are copied down to row 50. Try it out. Change some dates, change the month criteria then run the macro. The UDF, being dynamic, will automatically rebuild the address list. You could make the macro a little easier to run by putting a button on the sheet and attaching the macro to the button. You may have to change some of the references in the macro to meet your actual layout. Post back if you need help doing that. Biff "SteveT" wrote in message ... A sample file would be easiest, but it wouldn't help other people who may find this thread after the file is removed. So either way! "T. Valko" wrote: Ok.... Would you like me to post a sample file or try to explain it all in writing? Biff "SteveT" wrote in message ... Hi Biff. Yes, it is all related to the same problem. I have been messing around with it trying various ways to get what I want and got stuck once again. I am definitely interested in what you have figured out. Performance isn't a big issue because it is something that only has to be run once a month and I would rather have the computer sit and work for an hour then have to go through all these records and manually create the lists. Thank you so much for your help! "T. Valko" wrote: Hi! I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
Thank you so much! I am going to go with the dynamic UDF.
"T. Valko" wrote: Ok, here goes....... Here is the sample file: SteveT.xls 76kb http://cjoint.com/?czgYgNgNxj The file contains VBA code. 1 macro provided by Tom Ogilvy and a UDF provided by Chip Pearson. Beleive it or not the hardest thing about this was getting the addresses the way you wanted them. I had originally used some helper columns to create an array of the addresses and then used another formula to transpose those into a single column. This worked but was clunky! So I went over to the programming group and got some help by some of the good folks over there. Here's a link to that thread: http://tinyurl.com/2hajwo I've used both the macro and the UDF in this file. The macro was used on sheet Dest and the UDF was used on sheet UDF. In the sample file I've added a column to count how many dates in each address meet the month criteria entered in cell A2 of sheet Data. This column is used by both the macro and the UDF. The macro is faster but is not dynamic. The UDF is slower but is dynamic. You'll have to decide which method to use. I kind of like things being dynamic so my tendency would be towards the UDF. See the linked thread above for Chip's instructions on the UDF. The macro is module 1 and the UDF is module 2 The formulas I've used contain named ranges. On both the Dest sheet and the UDF sheet the formulas are copied down to row 50. Try it out. Change some dates, change the month criteria then run the macro. The UDF, being dynamic, will automatically rebuild the address list. You could make the macro a little easier to run by putting a button on the sheet and attaching the macro to the button. You may have to change some of the references in the macro to meet your actual layout. Post back if you need help doing that. Biff "SteveT" wrote in message ... A sample file would be easiest, but it wouldn't help other people who may find this thread after the file is removed. So either way! "T. Valko" wrote: Ok.... Would you like me to post a sample file or try to explain it all in writing? Biff "SteveT" wrote in message ... Hi Biff. Yes, it is all related to the same problem. I have been messing around with it trying various ways to get what I want and got stuck once again. I am definitely interested in what you have figured out. Performance isn't a big issue because it is something that only has to be run once a month and I would rather have the computer sit and work for an hour then have to go through all these records and manually create the lists. Thank you so much for your help! "T. Valko" wrote: Hi! I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search row then column or column then row
You're welcome. Thanks for the feedback!
Let's also thank Tom and Chip for the assist! Biff "SteveT" wrote in message ... Thank you so much! I am going to go with the dynamic UDF. "T. Valko" wrote: Ok, here goes....... Here is the sample file: SteveT.xls 76kb http://cjoint.com/?czgYgNgNxj The file contains VBA code. 1 macro provided by Tom Ogilvy and a UDF provided by Chip Pearson. Beleive it or not the hardest thing about this was getting the addresses the way you wanted them. I had originally used some helper columns to create an array of the addresses and then used another formula to transpose those into a single column. This worked but was clunky! So I went over to the programming group and got some help by some of the good folks over there. Here's a link to that thread: http://tinyurl.com/2hajwo I've used both the macro and the UDF in this file. The macro was used on sheet Dest and the UDF was used on sheet UDF. In the sample file I've added a column to count how many dates in each address meet the month criteria entered in cell A2 of sheet Data. This column is used by both the macro and the UDF. The macro is faster but is not dynamic. The UDF is slower but is dynamic. You'll have to decide which method to use. I kind of like things being dynamic so my tendency would be towards the UDF. See the linked thread above for Chip's instructions on the UDF. The macro is module 1 and the UDF is module 2 The formulas I've used contain named ranges. On both the Dest sheet and the UDF sheet the formulas are copied down to row 50. Try it out. Change some dates, change the month criteria then run the macro. The UDF, being dynamic, will automatically rebuild the address list. You could make the macro a little easier to run by putting a button on the sheet and attaching the macro to the button. You may have to change some of the references in the macro to meet your actual layout. Post back if you need help doing that. Biff "SteveT" wrote in message ... A sample file would be easiest, but it wouldn't help other people who may find this thread after the file is removed. So either way! "T. Valko" wrote: Ok.... Would you like me to post a sample file or try to explain it all in writing? Biff "SteveT" wrote in message ... Hi Biff. Yes, it is all related to the same problem. I have been messing around with it trying various ways to get what I want and got stuck once again. I am definitely interested in what you have figured out. Performance isn't a big issue because it is something that only has to be run once a month and I would rather have the computer sit and work for an hour then have to go through all these records and manually create the lists. Thank you so much for your help! "T. Valko" wrote: Hi! I saw this post and it prompted me to work on your other post. I assume this post is related to that other post? I have a solution to your other post but it is definitely not very elegant, but it works. It takes a few steps and uses a few formulas, one of which is somewhat complicated. Is this a one time process? In your other post you said you had ~800 addresses and ~20 inspection dates. That means there are 16,000 possible matches. 16,000 of these formulas will impact the performance of your file. Are you interested? Biff "SteveT" wrote in message ... I am searching a talbe for dates that match a given month and ned to find the row and column of the cells that match. I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,ROW($C$5:$E$12)) ,ROW(1:1)) It correctly returns all the rows whose dates match the right month I have this array function... =SMALL(IF(MONTH($C$5:$E$12)=$D$31,COLUMN($C$5:$E$1 2)),ROW(1:1)) It correctly returns all the columns whose dates match the right month The problem is that the one returning the rows searches rows then columns to report the data and the one returning the columns searches columns then rows to report the data. Is there a way to force one of them to look the other way? Thank in advance for any help Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How to search a column that is not the first of the table | Excel Worksheet Functions | |||
Column list search | Excel Worksheet Functions | |||
two column search | Excel Discussion (Misc queries) | |||
Search one column and return value from next column | Excel Discussion (Misc queries) |