Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically extract only relevant data from a worksheet into a new one?
Hello,
I need to retrieve data matching particular criteria from a worksheet. All "matching" cells should be inserted in a new worksheet (essentially a "cleaned" sheet containing ONLY the relevant data) All cells that do not match the criteria should be "skipped" completely, NOT just left blank. It needs to be done automatically, not requiring any user interaction (so data sorting won't work) Example: I have data in the following form in Worksheet 1 A x1 x2 x3 x4 B y1 y2 y3 y4 C x1 x2 x3 x4 D z1 z2 z3 z4 E x1 x2 x3 x4 F u1 u2 u3 u4 I'm only interested in the data of type "x" (so only rows A, C, E match). I want Worksheet 2 to contain ONLY this relevant data: A x1 x2 x3 x4 C x1 x2 x3 x4 E x1 x2 x3 x4 eg WITHOUT empty cells for the "non-matching" data. If Worksheet 1 is modified, Worksheet 2 should reflect the changes automatically (for example if the data in row C is replaced with data of type "w", Worksheet 2 should only contain: A x1 x2 x3 x4 E x1 x2 x3 x4 Skipping individual cells would be even better! eg: Worksheet 1: A x1 y2 x3 x4 B y1 x2 x3 y4 C y1 y2 y3 y4 to Worksheet 2: A x1 x3 x4 B x2 x3 Can this be done, ideally while only using formulae? Execution speed is not really an issue. ( I used rows for this example, data could also be in columns) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically extract only relevant data from a worksheet into a new one?
One formulas play which would deliver it here ..
(neat bunching of extracts to the left and at the top in the results sheet) Assume source data in sheet: S, cols A to D, from row1 down Assume the extraction criteria for each row in S is simply the leftmost character (eg: x) In a new sheet: 1, In A1: =IF(LEFT(S!A1)="x",COLUMN(),"") Copy across to D1, fill down to cover the max extent of data in S In a new sheet: 2, In A1: =INDEX(S!1:1,SMALL('1'!1:1,COLUMNS($A:A))) Copy across/fill down to the same extent as done in sheet: 1 (Hide away sheets: 1 & 2) Then in a new sheet: R, (R=results) In A1: =IF(ISERROR('2'!A1),"",ROW()) In B1: =IF(OR(ROW()COUNT($A:$A),ISERROR(INDEX('2'!A:A,SM ALL($A:$A,ROW())))),"",INDEX('2'!A:A,SMALL($A:$A,R OW()))) Copy B1 to E1. Select A1:E1, Copy down to the same extent as done in sheet: 1. Hide away col A. Cols B to E will auto-return the required results, with all lines neatly bunched at the top and with data neatly bunched to the left in each line. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MCSmarties" wrote in message ups.com... Hello, I need to retrieve data matching particular criteria from a worksheet. All "matching" cells should be inserted in a new worksheet (essentially a "cleaned" sheet containing ONLY the relevant data) All cells that do not match the criteria should be "skipped" completely, NOT just left blank. It needs to be done automatically, not requiring any user interaction (so data sorting won't work) Example: I have data in the following form in Worksheet 1 A x1 x2 x3 x4 B y1 y2 y3 y4 C x1 x2 x3 x4 D z1 z2 z3 z4 E x1 x2 x3 x4 F u1 u2 u3 u4 I'm only interested in the data of type "x" (so only rows A, C, E match). I want Worksheet 2 to contain ONLY this relevant data: A x1 x2 x3 x4 C x1 x2 x3 x4 E x1 x2 x3 x4 eg WITHOUT empty cells for the "non-matching" data. If Worksheet 1 is modified, Worksheet 2 should reflect the changes automatically (for example if the data in row C is replaced with data of type "w", Worksheet 2 should only contain: A x1 x2 x3 x4 E x1 x2 x3 x4 Skipping individual cells would be even better! eg: Worksheet 1: A x1 y2 x3 x4 B y1 x2 x3 y4 C y1 y2 y3 y4 to Worksheet 2: A x1 x3 x4 B x2 x3 Can this be done, ideally while only using formulae? Execution speed is not really an issue. ( I used rows for this example, data could also be in columns) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically extract only relevant data from a worksheet into a new one?
On Oct 5, 1:03 am, "Max" wrote:
One formulas play which would deliver it here .. (neat bunching of extracts to the left and at the top in the results sheet) Assume source data in sheet: S, cols A to D, from row1 down Assume the extraction criteria for each row in S is simply the leftmost character (eg: x) In a new sheet: 1, In A1: =IF(LEFT(S!A1)="x",COLUMN(),"") Copy across to D1, fill down to cover the max extent of data in S In a new sheet: 2, In A1: =INDEX(S!1:1,SMALL('1'!1:1,COLUMNS($A:A))) Copy across/fill down to the same extent as done in sheet: 1 (Hide away sheets: 1 & 2) Then in a new sheet: R, (R=results) In A1: =IF(ISERROR('2'!A1),"",ROW()) In B1: =IF(OR(ROW()COUNT($A:$A),ISERROR(INDEX('2'!A:A,SM ALL($A:$A,ROW())))),"",INDEX('2'!A:A,SMALL($A:$A,R OW()))) Copy B1 to E1. Select A1:E1, Copy down to the same extent as done in sheet: 1. Hide away col A. Cols B to E will auto-return the required results, with all lines neatly bunched at the top and with data neatly bunched to the left in each line. Hello, I need to retrieve data matching particular criteria from a worksheet. All "matching" cells should be inserted in a new worksheet (essentially a "cleaned" sheet containing ONLY the relevant data) All cells that do not match the criteria should be "skipped" completely, NOT just left blank. It needs to be done automatically, not requiring any user interaction (so data sorting won't work) (...) Thanks a lot for the quick reply and the awesome tip, it works! However, the current setup _requires_ the original data to be starting at A1. I think it's because of the INDEX(S!1:1) part of the function in sheet 2. How can I modify this to make it work for any range in sheet S? eg, instead of having data from A1:D4 having it for example in G6:J10 (or whatever) Ideally, one could move the data to somewhere else in S and sheet 2 (and hence sheet R) would be updated. It would make it much more versatile! I can of course explicitely define a range INDEX(S!G6:G6) and take it from there, but that kills the "adaptability" part. Maybe by using LOOKUP and OFFSET to find the initial data - but how can I do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically extract only relevant data from a worksheet into a new one?
How can I modify this to make it work for any range in sheet S?
Assume source data is a defined range: MyR In sheet: 1, In A1: =IF(LEFT(INDEX(MyR,ROWS($1:1),COLUMNS($A:A)))="x", COLUMN(),"") Copy across/fill down to cover the extent of MyR In a sheet: 2, In A1: =INDEX(T(OFFSET(MyR,ROWS($1:1)-1,COLUMN($A:$D)-1)),SMALL('1'!1:1,COLUMNS($A:A))) Copy across/fill down to cover the extent of MyR Sheet: R = no change to formulas Just ensure the B1 copy across & row fill down covers the extent of MyR -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MCSmarties" wrote Thanks a lot for the quick reply and the awesome tip, it works! However, the current setup _requires_ the original data to be starting at A1. I think it's because of the INDEX(S!1:1) part of the function in sheet 2. How can I modify this to make it work for any range in sheet S? eg, instead of having data from A1:D4 having it for example in G6:J10 (or whatever) Ideally, one could move the data to somewhere else in S and sheet 2 (and hence sheet R) would be updated. It would make it much more versatile! I can of course explicitely define a range INDEX(S!G6:G6) and take it from there, but that kills the "adaptability" part. Maybe by using LOOKUP and OFFSET to find the initial data - but how can I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract data from one worksheet | Excel Worksheet Functions | |||
Using a column of data from 1 worksheet to extract data from another worksheet | Excel Worksheet Functions | |||
Extract data from one Worksheet to another | Excel Worksheet Functions | |||
Copying matched criteria plus relevant columns to new worksheet | Excel Discussion (Misc queries) | |||
extract data from worksheet | Excel Worksheet Functions |