Copy rows from two sheets to another based on criteria

Hi

I have two sheets with the exact same column headers and data that changes constantly. I need a formula that will "search" both sheets for a specific word in one of the columns and copy the rows that has that word to the third sheet.

Dont know if i'm explianing it correctly.... but how it should work is, if i type the word into "A1" of the third sheet, all the rows that contain that word in the specified column in sheet1 & 2 must "copy" to the third sheet from "A3" down.

Example : (criteria = hi
Sheet1 Sheet2 Sheet3
Column1 Column2 Column3 Column1 Column2 Column3 Column1 Column2 Column3
1 hi 2 3 ho 4 1 hi 2
5 ho 6 7 hi 8 7 hi 8

I don't know if this will have any bearing, but the actual sheets are 9 columns and the data goes as far as row 5000 in each of the two "search" sheets.

Any help will be greatly appreciated.

See attached example sheet, the result i need is on sheet3 with discriptions.

this is perfect! tahnks for your efforts!

when on of the criteria is aken away in one of the sheets and no longer fits the criteria it shows a ref error. is there a way to display that ref error as an empty cell?

Never mind..... ID ten T moment. i can use conditional formatting to "hide" the ref error.

thanks again Jacc. 1million rep coming our way

Hi

Sorry i took so long to respond

Thanks for all the help so far.

Try as i might, i cannot get this to work properly in my workbook, although it did exactly what i wanted in the first sheet you sent.

I have attached the actual workbook that i will be using. sheets "EXISING CLIENTS" & "PC CLIENTS" cannot be changed except for adding data i.e. columns cannot be added as this affects the userform.

Your formulas should do their work in "Sheet1".

The idea with the formulas you provide is to be able to type a specific suburb into textbox2 on the userform, in turn, the userform will paste the suburb name into "Sheet1", this must then be used as a criteria for your formulas which then basically "extracts" the rows that contain said suburb into "Sheet1" where i will be able to make it display in the userform.

The data entry sheets "EXISING CLIENTS" & "PC CLIENTS" will go to about row 5000 in each sheet and in "Sheet1" i need at least 50 records to show for the given criteria.

I hope i am explaining this corectly and my apologies if it's a bit long winded

yours truly
excel DumbAss

Bump..................

and bunp again......

gr8 thanks. will test the sweet cr@p out of it and get back to you.

"You must spread some Reputation around before giving it to Jacc again." well...... THAT SUCKS. looks like i'll have to give the million reps in slooooooow increments, the site does not allow me to give it all at once.

I cannot enable editing nat the moment on the sheet, work PC , so i'll test it tonight.

Thank you very much for your patience and all the help.

Please Login or Register  to view this content.

So, it looks like i'll have to give that million rep in sloooooooow increments lol
unfortunately i'm at work so the sheet won't enable editing, so i'll have to wait 'till tonight to test it. for now i'll mark this as solved (taken up enough of your time with this )
i'll PM you with how it went.

......and thanks again for all the help.