Search and Display functions

[mialls]

Ah, Friday afternoons...

Looking for a formula or a VBA function which can search through a column of data, which will have multiple identical entries, and display all results in another location. (Due to the multiple identical entries, VLOOKUP does not appear to be useful.)

Example : Database has two columns; one for "DATE" and one for "TIME".
I would like to create a function which will allow someone to input "DATE" and display all the "TIME" information.

March 31 - 8:00am
March 31 - 8:30am
March 31 - 8:32am
etc.

What I've come up with for steps in this is (assuming variable is found) :
1) Input search variable
2) Search Column for search variable.
3) Count number of times search variable is found.
4) Display results 1-N.

The first three I've figured out. The fourth is where I'm at a loss - displaying a single result is easy, but I've hit a stumbling block as to how to get the function to move down to the next one it may find in a search. Any help would be appreciated.

(Name-calling and mockery is also acceptable, provided that at the end of the name-calling and mockery there's some sort of solution provided. )

 

[hiker95]

mialls,


Sample raw data in worksheet Database:

Excel Workbook
	A	B
1	Dates	Times
2	March 30	10:00 AM
3	March 30	11:00 AM
4	March 30	12:00 PM
5	March 30	3:00 PM
6	March 31	8:00 AM
7	March 31	8:30 AM
8	March 31	8:32 AM
9
Database

Sample worksheet Sheet1, with the Search Date in cell A2:

Excel Workbook
	A	B	C
1	Search Date	Times Found	3
2	March 31	8:00 AM
3		8:30 AM
4		8:32 AM
5
6
7
8
9
10
11
Sheet1

Cell C3 contains a counter of how many times the entry in cell A2, March 31 is found in worksheet Database, column A.


Copy the following array formula into cell B2, confirmed with CTRL + SHIFT + ENTER (not just ENTER), and copied down the number of times displayed in cell C3:

=IF(ISERROR(INDEX(Database!$B$2:$B$100,SMALL(IF(Database!$A$2:$A$100=$A$2,ROW(Database!$B$2:$B$100)-ROW(Database!$B$2)+1),ROWS(Database!$B$2:B2)))),"",INDEX(Database!$B$2:$B$100,SMALL(IF(Database!$A$2:$A$100=$A$2,ROW(Database!$B$2:$B$100)-ROW(Database!$B$2)+1),ROWS(Database!$B$2:B2))))

 

[mialls]

Thank you for this reply; it appears to answer my need almost perfectly.

However, I've run into a rather annoying problem while adapting it to my individual needs - in my original file, I can't seem to enter it as as described anymore. CTRL-SHIFT-ENTER, which worked fine the first time, now doesn't seem to work; I get a box full of number signs (#). I had adjusted it to look through more than 100 cells (to B1000) and recieved this error. Thinking that perhaps I had just run into a situation where there was a limit on the amount of cells it could encompass, I tried to redo everything and start fresh.

Unfortunately I can't even redo the original solution in the same file; Deleting contents, Clearing contents, new worksheets, inserting brand new worksheets into the file, even going so far as to reenter all of the input data and the forumla, all came with the same result; (##) for as wide as I made the cell, and copy/paste that cell to an entirely different program (Notepad, MS Word).

In trying a new file entirely, I was able to begin again, and it is not giving me the same (##) error. Is this error a known issue with a solution, or is it just one of those reasons that one should always have a working file when testing new things like this? (which fortunately I do!) Also, is there an upper maximum limit to the number of cells that can be included - can "Database!$B$2:$B$100" be expanded to "Database!$B$2:$B$1,000" or "Database!$B$2:$B$10,000"?

As it is likely important, I am working with MS Excel 2002 (10.5313.4219) SP-2 and there is no plans to upgrade to a newer version of Excel in the timeframe of this project. (yay companies who are too big to be able to afford to upgrade everyone to a newer software version. sigh.)

 

[hiker95]

mialls,


You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net