Automatically extract only relevant data from a worksheet into a new one?

  • Thread starter Thread starter MCSmarties
  • Start date Start date
M

MCSmarties

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)
 
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,SMALL($A:$A,ROW())))),"",INDEX('2'!A:A,SMALL($A:$A,ROW())))
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.
 
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,SMALL($A:$A,ROW())))),"",INDEX('2'!A:A,SMALL($A:$A,ROW())))
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.
Click to expand...
(...)

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?
 
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
 
Back
Top