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

 

[T. Valko]

p19 - t 23 sorry. I still need to loose column I and o though

Ok, I'm looking at your file.

There sure is a lot of color! It looks like each section is color coded. I'm a "minimalist" so I would use as little formatting as possible.

Is the "Enter Reg" that "junk entry" you talked about using?

What are you trying to accomplish when hiding certain columns?

 

[scareypirate]

Ok, I'm looking at your file.

There sure is a lot of color! It looks like each section is color coded. I'm a "minimalist" so I would use as little formatting as possible.

Is the "Enter Reg" that "junk entry" you talked about using?

What are you trying to accomplish when hiding certain columns?

Hiya mate. Yes I can loose the colour no problem I agree it looks too busy with the colour there.

"Enter Reg" is the junk just to fill the spaces. I'd sooner have them blank but I believe you mentioned I wouldn't be able to have blank cells if hidding columns or something like that.

I am trying to replicate my paper entry using excel as paper is not practical anymore as when searching for a registration and locating it takes an age when looking for it. So a search function instantly finds an entry, where its located and give me a number for that vehicle.

As you can see from my sheet I am almost there (thanks to you of course). But we have no provision for the letter I or the letter O. So I would need to exclude those from the numbering system.

row A = 1 to 10
row B = 11 to 20
row C = 21 to 30

and so on. But if I just hide column I & O they still get given a number so I would like a way to completely take I & O out so A|B|C|D|E|F|G|H|J|K|L|M|N|P|Q|R|S|T

I think I may be asking too much here.

 

[T. Valko]

Hiya mate. Yes I can loose the colour no problem I agree it looks too busy with the colour there.

"Enter Reg" is the junk just to fill the spaces. I'd sooner have them blank but I believe you mentioned I wouldn't be able to have blank cells if hidding columns or something like that.

I am trying to replicate my paper entry using excel as paper is not practical anymore as when searching for a registration and locating it takes an age when looking for it. So a search function instantly finds an entry, where its located and give me a number for that vehicle.

As you can see from my sheet I am almost there (thanks to you of course). But we have no provision for the letter I or the letter O. So I would need to exclude those from the numbering system.

row A = 1 to 10
row B = 11 to 20
row C = 21 to 30

and so on. But if I just hide column I & O they still get given a number so I would like a way to completely take I & O out so A|B|C|D|E|F|G|H|J|K|L|M|N|P|Q|R|S|T

I think I may be asking too much here.

Ok, about not having empty cells...

That was based on an idea I had but that didn't work out due to the hidden column problem. So, if you want to you can clear out all the "Enter Reg" entries.

Now, about wanting to "completely take I & O out"...

Right now columns I and O are the only columns that are full of "Enter Reg". So, if we get rid of those then both columns I and O will be completely empty.

How does this affect the result? If I search for S504 YNP the cell address is N5 and the lot number is 135 which is correct, isn't it?

 

[scareypirate]

Ok, about not having empty cells...

That was based on an idea I had but that didn't work out due to the hidden column problem. So, if you want to you can clear out all the "Enter Reg" entries.

Now, about wanting to "completely take I & O out"...

Right now columns I and O are the only columns that are full of "Enter Reg". So, if we get rid of those then both columns I and O will be completely empty.

How does this affect the result? If I search for S504 YNP the cell address is N5 and the lot number is 135 which is correct, isn't it?

Ok I have removed all of the "Enter Reg", thanks for putting me straight

When you searched for 'S504 YNP' the lot number should be 125 and not 135 as column I should not be allocated a number as theoreticaly it doesn't exist. In the example sheet you have I think column I had a number at the bottom which it wouldn't have so therefore column J takes the next number in the sequence.

Sorry if this is a headache for you buddy. But I have to say you have been a real help and may I add very understanding too.

 

[T. Valko]

Ok I have removed all of the "Enter Reg", thanks for putting me straight

When you searched for 'S504 YNP' the lot number should be 125 and not 135 as column I should not be allocated a number as theoreticaly it doesn't exist. In the example sheet you have I think column I had a number at the bottom which it wouldn't have so therefore column J takes the next number in the sequence.

Sorry if this is a headache for you buddy. But I have to say you have been a real help and may I add very understanding too.

Ok, so in practice you will actually be hiding certain columns?

What determines if/when a column gets hidden?

 

[scareypirate]

Only that column I and O will never be used and therefore will always be hidden. The rest of the columns up to T will be all I require

 

[T. Valko]

Only that column I and O will never be used and therefore will always be hidden. The rest of the columns up to T will be all I require

Ok, I think I have it.

All we need to do is check the cell address. If it's after column O then we use a correction factor of -20 and if the cell address is after column I then we use a correction factor of -10.

Array entered**:

=MATCH(2,1/FREQUENCY(1,--(TRANSPOSE(A1:T10=C15))))-IF(LEFT(L15)>"O",20,IF(LEFT(L15)>"I",10,0))

** 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]

Ok, I think I have it.

All we need to do is check the cell address. If it's after column O then we use a correction factor of -20 and if the cell address is after column I then we use a correction factor of -10.

Array entered**:

=MATCH(2,1/FREQUENCY(1,--(TRANSPOSE(A1:T10=C15))))-IF(LEFT(L15)>"O",20,IF(LEFT(L15)>"I",10,0))

** 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.

Man you almost got it thank you very much. Only problem is that I get a lot number of O? when I search the P column.

This is what I have for the lot number: =ADDRESS(MIN(IF(A1:T10=C15,ROW(A1:T10))),MIN(IF(A1:T10=C15,COLUMN(A1:T10))),4) as an array. This happens to P1 only :\

You are brilliant thank you so much

 

[T. Valko]

Man you almost got it thank you very much. Only problem is that I get a lot number of O? when I search the P column.

This is what I have for the lot number: =ADDRESS(MIN(IF(A1:T10=C15,ROW(A1:T10))),MIN(IF(A1:T10=C15,COLUMN(A1:T10))),4) as an array. This happens to P1 only :\

You are brilliant thank you so much

Seems to be working OK for me.

If I seach for R310 TXX I get Location: P5 and Lot No: 135.

If I enter X in cell P1 and then seach for X I get Location: P1 and Lot No: 131.

 

[scareypirate]

Seems to be working OK for me.

If I seach for R310 TXX I get Location: P5 and Lot No: 135.

If I enter X in cell P1 and then seach for X I get Location: P1 and Lot No: 131.

Hmmm. Can I forward you might sheet again? Only I have tweaked it and not yet got rid of the colour but my P1 results in O1. Not sure why if it's working your side. Must be me