user form to search with two criterias

[punna111]

Hi all,

Please find the attachment.
I have user form which needs to be search with two criterias.
i didn't find perfect code for this. if i will search with one criteria it will give all the results from that particular column or what ever i selected column with combo box.
if i select address 1 in combo box it should search in address 1 column only.

but if i search with two criterias user form should search with two criterias in a row. result row should satisfy the two criterias.

can anyone help on this issue. i am searching for this from 2 months.

Thanks,
PRB.

[Frasterist]

Nice form. Easy to understand. It might help if we knew what the search criteria were going to be and whether or not they will be the same every time.

[punna111]

Hi,

Search criteria will be different every time.

see i am searching with criteria name:excel, address2:5620- and it should show the row 4.

if i search address1:1378, city:portland- then it should show the row 24.

Regards,
PRB.

[Frasterist]

Maybe I'm trying to make this more difficult than it needs to be.

If you want to find "5620", that's all you need to use as your seach paramater. You don't need "excel, address2:". It would never find that. It will only find one cell at a time containing the exact text string you've stipulated. The shorter the text string, the better (usually). If you type the entire address and add or leave out anything (an extra space, for instance), your search will fail. Maye you're leaving out some details.

.

[punna111]

Hi,

thank you for your reply.

If i am able to find with built in find & replace form in excel why do i need another form.
Now i will explain the actual scenario to better understand.
I have an excel file with more than 400 MB, which contains more than 4,00,000 rows.

Ex: i need name: excel with address 5620....etc.

if i search for "excel" with ctrl+f it will show more than 1000 occurrences in the 4,00,000 rows. if i search the 5620 in ctrl+f it will show more than 1000 results, where i can't go to the every result and i can't find out what exactly i need. To narrow my search criteria i am asking two creterias.

where if i select name in combobox that should search only in the name column.
if i select address1 in combobox it should search only in the address1 column. when it is giving the result of a row. two conditions must be satisfied. it means if it is giving some 10 th row as result in the 10 th row name should be excel and address1 should be 5620...

userforms will find out with multiple creterias. this user form is only for multiple criteria purpose i have given. i have another user which is the actual one with code. i found this code on the net only. i did some customization but i am not able to write the exact code for that user for to satisfy the two conditions. i am attaching that sheet. please find.

[punna111]

No reply on this...i am worried..

[Frasterist]

Note: The "lakh" is a term without universal understanding, mostly in India and maybe Pakistan; and I'm not even sure of that. Other parts of the world will not understand it. Best to keep to real numbers.


<If i am able to find with built in find & replace form in excel why do i need another form.> In my example, you don't need another form; so, I deleted sheets 2,3, & 4 to make the point.


I have attached your "Copy of multiple search.xlsm" file with some changes; so, I renamed the file "sample 1.xlsm". I added two combo boxes to choose the criteria and Cell A2 will display the result. The result is the "V. No." you listed in column A of the row that contains the match. You choose the criterion in one drop-down first; and, if the criterion chosen in the other drop-down is a match on the same row, you will get a "V. No." result. If no match, you get some sort of error.

No VBA code was used in this sample and I removed the user form that wasn't needed.

If this is what you're looking for, you should be able to copy the formulae to your database and make the changes as needed. Otherwise, I don't understand.

[punna111]

Thank you for your post Frasterist.


The sheet which you have given is didn't reach my requirement. because i want to search a name with some address. at that point of time as per your sheet i need to select the name from the combobox1 and then address from combo box 2. the sheet which i have provided is very short. and i already informed the size of the sheet and the rows. when i have more than 4,00,000 rows i can't go to the combo box to select a particular name or address. that is nothing but seeing all the total rows. and more over we can't type any letter to search in the above combo boxes.

here i have given just one example for criteria with name and address. I may search with v name and city or v ta id and zip code etc. if i have a user form i can select any criteria as i have given v name or v address or v city etc and i can type single word and i can search.

[Frasterist]

Sorry I wasted my time putting in the combo boxes. I thought they were ridiculous from the beginning. I wouldn’t have done that if you hadn’t mentioned “what ever i selected column with combo box” (your exact words, copied and pasted from your original post).

I see a recurring practice here in that no one seems to proofread his post, which leaves incomprehensible grammar and incomplete sentences that don’t make sense, cell references that are erroneous and inconsistent, and careless and contradictory statements, which makes interpreting the project difficult and time wasting. In my first post here, I didn't include a sample file, which is another common mistake.

I removed the combo boxes so now you only have to type in the parameter. Keep in mind, it must be exact. Any deviation will return an error. That means no extra or missing spaces, missing or extra commas, hyphens, or periods. “Happy St.” or "Happy-St" will not find “Happy St”. “McMaster Street” will not find “Mc Master St”. Accuracy can be very difficult with addresses. I know this because I deal with addresses every day because I have to include Postal Intelligent Bar Codes on hundreds of pieces of mail every week. It’s more difficult that one might anticipate. Fortunately, upper or lower case doesn't matter. I typed in "rOgEr" instead of "Roger" in the sample file to demonstrate that point.

sample 2.xlsm attached.

[punna111]

I see a recurring practice here in that no one seems to proofread his post, which leaves incomprehensible grammar and incomplete sentences that don’t make sense, cell references that are erroneous and inconsistent, and careless and contradictory statements, which makes interpreting the project difficult and time wasting. In my first post here, I didn't include a sample file, which is another common mistake.


.

Hi,

I am not able to understand these lines could you please explain.. i am looking at sheet now.