Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel formula


  • Please log in to reply
2 replies to this topic

#1 Geoff777

Geoff777

  • Members
  • 238 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:01:00 AM

Posted 24 July 2009 - 08:03 AM

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
Geoff

BC AdBot (Login to Remove)

 


#2 Chris S.

Chris S.

  • Members
  • 13 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Queens, NY
  • Local time:08:00 PM

Posted 24 July 2009 - 12:53 PM

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

#3 Geoff777

Geoff777
  • Topic Starter

  • Members
  • 238 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:01:00 AM

Posted 25 July 2009 - 01:38 AM

Hi Chris,

That worked absolutely brilliant! :thumbsup:

Thanks very much
Geoff




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users