Search function

[scareypirate]

Hi peeps. I really need some help here and know this is the place to ask.

What I need is as follows:

I have a grid of car registration numbers. I need the ability to have a search box and enter a car registration number. If a match is found I need it to give me the location of that reg number.

Example:

Search 'WJ05ASP' would return 'A4' as WJ05ASP is located in the column A Row 4.

I have searched here but can't seem to find anyone asking this.

Thanks anyone that can help me here. And if you require a excel attach example to play with then I will provide one.

All the best

 

[scareypirate]

WOW!!!! It worked cheers to you guy's. Thank you very much you are brilliant thanks.

Oh now it works I had a bit of a brainwave and wondered if it is at all possible to display a number along side the co-ords?

I don't want to push it with you guy's but can it be done?

Example:

a b c d
1 aaa bbb ccc ddd
2 eee fff ggg hhh
3 iii jjj kkk lll
1 21 31 41

SO b1 = 21 and b2 = 22 and so on.
c1 = 31 and c2 = 32 and so on.

Thanks tons for the info guy's really appreciated

 

[T. Valko]

WOW!!!! It worked cheers to you guy's. Thank you very much you are brilliant thanks.

Oh now it works I had a bit of a brainwave and wondered if it is at all possible to display a number along side the co-ords?

I don't want to push it with you guy's but can it be done?

Example:

a b c d
1 aaa bbb ccc ddd
2 eee fff ggg hhh
3 iii jjj kkk lll
1 21 31 41

SO b1 = 21 and b2 = 22 and so on.
c1 = 31 and c2 = 32 and so on.

Thanks tons for the info guy's really appreciated

Do you mean that if you want to look up bbb you want the numeric co-ordinates for that cell?

This can probably be done if you can explain the logic of the co-ordinate numbering system.

 

[scareypirate]

Hiya mate thanks for the reply.

Yes Is it possible to keep the original co-ords. i.e A5 etc and have the following.

Column A1-A10 (would be 1-10)
Column B1-B10 (would be 11-20)
Column C1-C10 (would be 21-30)

So is it possible to have something like the following:

Search and find item in B5 and show B5 co-ord (which I now have working thanks to you)

Also is it possible to show both B5 and it's number as above would be 15 and B8 would be 18 C6 would be 26.

Thanks for your expertise by the way

I can supply a link of the spreadsheet if it may make it clearer.

 

[T. Valko]

Hiya mate thanks for the reply.

Yes Is it possible to keep the original co-ords. i.e A5 etc and have the following.

Column A1-A10 (would be 1-10)
Column B1-B10 (would be 11-20)
Column C1-C10 (would be 21-30)

So is it possible to have something like the following:

Search and find item in B5 and show B5 co-ord (which I now have working thanks to you)

Also is it possible to show both B5 and it's number as above would be 15 and B8 would be 18 C6 would be 26.

Thanks for your expertise by the way

I can supply a link of the spreadsheet if it may make it clearer.

Try this...

Book1
	A	B	C
1	94	25	35
2	90	89	3
3	31	95	87
4	5	31	92
5	36	85	57
6	33	60	87
7	75	67	32
8	28	30	ZZZ
9	67	83	9
10	52	17	96
Sheet1

Find the cell number of the cell that contains ZZZ...

Array entered**:

=MATCH(2,1/FREQUENCY(1,--(TRANSPOSE(A1:C10="ZZZ"))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[scareypirate]

Try this...

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"><col style="width:72px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">94</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">35</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">90</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">89</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">31</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">95</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">87</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">31</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">92</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">36</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">85</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">57</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">33</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">60</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">87</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">75</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">67</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">32</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">28</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">30</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">ZZZ</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">67</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">83</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">9</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">52</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">17</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">96</td></tr></tbody></table>


Find the cell number of the cell that contains ZZZ...

Array entered**:

=MATCH(2,1/FREQUENCY(1,--(TRANSPOSE(A1:C10="ZZZ"))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

That works perfectly for zzz. But I would probably have upto 250 items. Would I have to have a formula for each one?

 

[T. Valko]

That works perfectly for zzz. But I would probably have upto 250 items. Would I have to have a formula for each one?

Yes, if you want the cell number for each item.

 

[scareypirate]

Thanks buddy, really appreciate your effort. I'll give the numbering a miss lol

All the best

 

[scareypirate]

Woohooo I sussed it out. If I substitute 'zzz' with C13 (the search box) it does it perfectly. Cheers mate your a star!!!!!!

 

[T. Valko]

Woohooo I sussed it out. If I substitute 'zzz' with C13 (the search box) it does it perfectly. Cheers mate your a star!!!!!!

You're welcome. Thanks for the feedback!

 

[scareypirate]

Hi, Sorry but I have been playing around with it and got it fairly well to the point I need.

I do have 1 more question to pose. Is it at all possible to ignore a column?

Example let's say all I need is columns A|B|C|D|F|G|H <- Column I is not required.

I'm using the formula =MATCH(2,1/FREQUENCY(1,--(TRANSPOSE(A1:C10="C13")))) and this returns the value of each column. If I hide column I the formula still calculates column I.

Cheers once again