Match 2 user inputs across 2 rows and copy row

Hi all!

Really stuck with something on a project we're putting together and hoping you guys can help, i'm by no means an excel/VBA expert and if this can be done using formula then i apologise for the post in this area.

So the sheet we have has a ton of columns, we're interested in having 2 search cells on a sheet away from our raw data sheet(same workbook) where 2 strings can be entered and if found, have the entire matched rows copied from the raw sheet to a new sheet.

It seems simple enough at first but whats getting me stuck is that the data from either user input can be found in either column.

So for example, the user may input "Water" in one search box and "Bottle" in the other. I need to find a way to search through columns A and B in the entire data sheet and find rows where Water AND Bottle are found on the same row in either column and either order (Water | Bottle or Bottle | Water) and return the rows onto a new sheet. Moving the data to make it easier isn't an option unfortunately

The extracted data rows would look like:

Water | Bottle | Other Data
Bottle | Water | Other Data
Bottle | Water | Other Data
Water | Bottle | Other Data

Im absolutely stuck, my google skills have failed me and im hoping you ladies and gents may have a solution. I've looked (briefly) to see if this has been asked before and couldn't find anything.

Any help is much appreciated.

Thanks,
Matt.

*Edited to clarify certain points

I think this could be accomplished with helper columns on the data sheet and a pivot table. We'll need to see more to make an assessment.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

It turns out that it was simple enough to set up a test case. Normally I (and others) don't have the time to set them up especially when there is a lot of data involved. In this case, not a lot of data or setup is required.

I have it all on one page so I could test it easier.

The data is in columns A:E with helper columns in F, G & H.

The formula in F =IF([@[Keyword 1]]=$K$3,[@[Keyword 2]]=$K$4,FALSE) - which essentially says if Keyword 1 matches the input Keyword 1 (Cell K3), then test to see if Keyword 2 matches the other Keyword. There is a similar formula in column G to do the test the other way.

Column H =OR([@[Match 1]],[@[Match 2]]) which says if either condition is True, then it's a good combination.

Columns O and Q are pivot tables in their own right. That they do is get a unique list of the keywords from the database. I overlaid them with a named dynamic range (sort of an advanced topic, read up on it here: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges) and used that for a list-type data validation (read up on that here: http://www.utteraccess.com/wiki/Data_Validation). The result is I get drop down lists for cells K3 and K4.

There are two issues with using a pivot table:

1. You have to refresh it after making your selections.

2. If the selections yield no results, then everything comes out FALSE and FALSE is the only option for the filter on the pivot table so EVERYTHING shows. It will be obvious if this happens. You won't be able to select TRUE for the pivot table filter.

You should have enough to adapt this to your situation. Let me know if you need help with the pivot table.

With macro.
Is that it ?

Sheet1 - conditions
Sheet2 - data (bottle, water)
Sheet3 - results