How can I move rows of data from one sheet to another based on testing

[wec43wec]

I have a file of data that is pulled down into Excel in a sheet that I will call "Raw Data Sheet".


This file contains data with approx 10 columns and up to 1,000 rows which could vary.

I have 4 other tabs (i'll call Sheet-1, Sheet-2, Sheet-3 and Sheet-4) which I need to pull data from the "Raw Data Sheet" (a row of data contains 10 columns) based on testing one field/column within the "Raw Data Sheet that will determine which of the 4 sheets the information would flow into.

Currently, a simply sort of data within the "Raw Data Sheet" and then pasting those rows within the respective 4 sheets that meet my condition / test has worked OK but I need to find a way to automate this process where after dropping the data in the Raw Data Sheet the other tabs will automatically pull this information. The vlookup function would work, but if would leave gaps (empty spaces) where the condition is NOT true.

Note: The test would be the value in column A being male or female. If male, then place the row of data into one of the 4 sheets indicated above which would be for each State. If male and the "state" is NY, the contents of that row (from the Raw Data Sheet" would go into (as an example) Sheet 1 which would be used for NY information.

If male and the "state" column is Fla, then, the row of information would go into (as an example) Sheet-2, which is used for Fla information.

Note: If female, the data is not moved to any of the 4 Sheets.

Example of columns in the "Raw Data Sheet".

A B C D E F - J
1 GENDER - STATE - CITY - NAME - AGE - ETC
2
3
5
6
TO
1,000

 

[lionelhill]

I'm not sure this is the solution you are looking for, but you could also set up a pivot table by gender and state, select only the gender you want, and then clicking on state entries will generate a new worksheet containing individual entries for that state only. It will mean one click per state. In future, you will also have to use "refresh data" to make sure your lists are up to date.