Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting cells from worksheet based on value of 2 cells
I have an excel worksheet that is 341 columns by approx 50,000 rows. In
column A each row is given an identifying letter of A-E based on the information contained within that row. The other 340 colums contain various further information. I want to be able to extract either the entire or ideally columns 2, 35, 37 and 269 in to a seperate workbook where column 1 is equal to D and column 37 is within a specied date period (37 contains date claim processed). I've had a few goes at doing this but unfortunately i'm by no means an excel expert and the only way i can get it to work is by using very basic IF/AND statements and pulling through the claims that meet the criteria and ignoring the ones that don't, unfortunately this means i'm left with thousands of blank rows and have tried to copy and paste a code on another thread that would remove these automatically and this works fine on a cut down version (say a few hundred rows) but when ran against the entire workbook of 50,000 takes a long time to compelte and i'm sure there must we a way of just extracting the information i want without the blank rows. Many Thanks Nathan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting cells from worksheet based on value of 2 cells
Nathan,
Select your table, and apply data filters. Then filter on column A for the value D, then on column 37, using custom: "greater than or equal to" and select the start date, then "less than or equal to" and the finish date. Then select your table, use Edit / Go to.... special visible cells only (or the equivalent in XL 2007 - not sure what the specific steps are, but the functionality is there) and then copy and paste in your other sheet. Then delete the extra columns. HTH, Bernie MS Excel MVP "nathanh" wrote in message ... I have an excel worksheet that is 341 columns by approx 50,000 rows. In column A each row is given an identifying letter of A-E based on the information contained within that row. The other 340 colums contain various further information. I want to be able to extract either the entire or ideally columns 2, 35, 37 and 269 in to a seperate workbook where column 1 is equal to D and column 37 is within a specied date period (37 contains date claim processed). I've had a few goes at doing this but unfortunately i'm by no means an excel expert and the only way i can get it to work is by using very basic IF/AND statements and pulling through the claims that meet the criteria and ignoring the ones that don't, unfortunately this means i'm left with thousands of blank rows and have tried to copy and paste a code on another thread that would remove these automatically and this works fine on a cut down version (say a few hundred rows) but when ran against the entire workbook of 50,000 takes a long time to compelte and i'm sure there must we a way of just extracting the information i want without the blank rows. Many Thanks Nathan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data from one worksheet based on another - VLookup? | Excel Discussion (Misc queries) | |||
How do I copy cells from one worksheet to another based on values | Excel Worksheet Functions | |||
Extracting Data from another worksheet based on user input | Excel Worksheet Functions | |||
Automatically populate cells based on info in another worksheet | Excel Worksheet Functions | |||
Filling a few cells based on the info from another worksheet. | Excel Worksheet Functions |