BleepingComputer.com: Excel formula

Hi,

I want to insert data from one worksheet to another in the same workbook.

Specifically, i want to execute the following procedure.

If the value of column E in worksheet 1 = "Smith"

copy that entire row to worksheet 2.

Basically my spreadsheet holds data on a number of names, and l want to segregate the data for each person into their own worksheet.

Sheet 1 has over 50,000 rows of data so a simple copy and paste is going to take a long time!

Any ideas please?

many thanks

Hi Geoff,

Is this a one time copy? If so, you can turn on Excel's data filter function on the first sheet, filter the data, and copy the results to a new sheet?

1. In Excel, select cell A1 of your data sheet and then: Data > Filter > AutoFilter.
2. Click Column E's down-arrow and select the criteria to show.
3. Select the filtered rows and copy / paste them elsewhere.

I'm not sure how to return an entire row with a formula, but you can return a part with a VLOOKUP function...

=VLOOKUP(A1, 'MyDataSheet'!$A$1:$D$50434,2,FALSE)

-A1 is your search criteria (add $ before A and 1 to make it static)
-The second part is where you're looking (always searching the first column selected)
-2 is what column to return (if you started in A it returns result from B )
-FALSE stops Excel from giving 'next best' answers.

-Chris

Hi Chris,

That worked absolutely brilliant!

Thanks very much