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

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

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

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

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

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

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

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