Search row then column or column then row

[Guest]

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$12)),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

 

[T. Valko]

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

 

[Guest]

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]

Ok....

Would you like me to post a sample file or try to explain it all in writing?

Biff

 

[Guest]

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]

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

 

[Guest]

Thank you so much! I am going to go with the dynamic UDF.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Let's also thank Tom and Chip for the assist!

Biff